sql - Return a value when a different value changes -

I have a query that returns the following returns, EXCEPT for the previous column, which I need to know How to do that surgeon For each given ObservationID , I will have to return the date on which the status changes; A CD () function that takes some situations and not just offset. Can it be done?

I need to calculate the column change date; It should be the last date that the situation was not the current situation.

  + --------------- + -------- + ----------- + - ------ + ------------- + | Overview | Region | Date | Position | Change date | & Lt; - this field + --------------- + -------- + ----------- + ------- - + ------------- + | 1 | 10 | 1/3/2012 | Ice | 1/4/2012 | | 2 | 10 | 1/4/2012 | Water | 1/6/2012 | | 3 | 10 | 1/5/2012 | Water | 1/6/2012 | | 4 | 10 | 1/6/2012 | Gas | 1/7/2012 | | 5 | 10 | 1/7/2012 | Ice | | | 6 | 20 | 2/6/2012 | Water | 2/10/2012 | | 7 | 20 | 2/7/2012 | Water | 2/10/2012 | | 8 | 20 | 2/8/2012 | Water | 2/10/2012 | | 9 | 20 | 2/9/2012 | Water | 2/10/2012 | | 10 | 20 | 2/10/2012 | Ice | | + --------------- + -------- + ----------- + -------- + ---  +) It can do in a compact way:  
  SQL & gt; 2 Selecting 1, 10, Date '2012-03-01', Table 4 in the form of all 4 selected 2, 10, dated '2012-04-01' from 'Ice' dual union (observation id, area, obs_date, status 2) Create, all 5 selected 3, 10, dated '2012-05-01' from 'water' dual union, all 6 selected 4, 10, dated '2012-06-01', from dual 'water' dual union 'Gas' Association All 7 Selection 5, 10, Date '2012-07-01', 'Ice' from Dual Union 8 All 6 Selected 6, 20, Date '2012-06-02', 'Water' from Dual Union All 9 selected 7, 20, Date '2012-07-02', 'Water' from Dual Union All 10 F The condemnation 8, 20, dated '2012-08-02', 'Water' all 11, 9, 20, dated '2012- 09-02' from Dual Union, selection of all 12 from 'Water' Dual Union 10, 20, date '2012-10-02', 'Ice' from Dual; The table was created in SQL & gt; Overview for positioning, position, position 2, 5 measures from 3 models 4 dimensions (area, obset, status) (observation oriented, Obs_date_date2, cast (zero as date) position rule) rule 6 (7 position [change , Any, any] = minutes (obs_date2) [cv (field), obs_date> cb (obs_date), position! = Cv (status)] 8) order 19; OBSERVATIONID OBS_DATE STATU STATUS_CH ------------- --------- ----- --------- 1 01-Mar-12 Snow 01-AP -12 2 01-APR-12 Water 01-Jun-12 3-01-May-12 Water 01-Jun-12 4 01-Jun-12 Gas 01-Jul-12 5 01-Jul-12 Ice 6 02-Jun- 12 Water 02-OCT-12 7 02-July-12 Water 02-OCT-12 8-02-Aug-12 Water 02-OCT-12 9-02-SEP-12 Water 02-OCT-12 10-02-Oct-12 Snow   
 Bella:  
 That is, we will give the dimension on the field, date and condition because we want to see the cells with the same area, but get the first date a C.  
 We must also measure the date so that I want to do an alias  obs_date2 , and we want a new column  status_change  to change the situation Are there.  
 This line is a line that does everything for us:  
  status_change [any, none, any] = minutes (obs_date2) [cv ( Area), obs_date & gt; Cv (obs_date), situation! = CV (status)]   
 It says, for our three dimensions, look at the rows with only that area ( cv (region), ) and View the rows where the date is the date of the existing row ( obs_date> cv (obs_date) ) and the status is different from the current row ( status)! Finally, the minimum date can be obtained, which satisfies this set of conditions ( minimum (obs_date2) ) and it will be  status_change  Specify at  Any, any, any  on the left means that this count applies to all rows.