Re: Compare dates

LALIT KUMAR wrote on 18.06.2011 20:31:
> Hi,
> I have following table data:
> site_id        village_name              Date              depth       flag
>
> W001         Akoli                        1991-01-31       3.5
> W001         Akoli                        1991-03-31       3.7
> W001         Akoli                        1991-05-31       4.35
> W001         Akoli                        1992-01-31        2.9
> W001         Akoli                        1992-03-31          4
> W001         Akoli                        1992-05-31        2.0
> W001         Akoli                        1992-10-31           2
> W0002       Awale                       1977-05-30        7.2
> W0002       Awale                       1977-10-30        3
> W003         Talwade                    1998-01-23          8
> W003         Talwade                    1998-03-23          9.2
> W003         Talwade                    1998-06-09          8.95
>
> For each village the following rule is to be followed.
>
> The depth in the month of may (05) must be more than depth given in the previous reading. If not so the flag field
shoulbe set 1 
>
> The tuple in green has month may(05) which has depth more than previous date depth.
>
> The red tuple has month may(05) but has depth less than previous depth.
>
> So the flag field is  to be set 1 here.
>
>
> Simiarly for other villages (i.e.) dates from two different villages will not be compared.

Something like:

SELECT site_id,
        village_name,
        date,
        depth,
        case
          when depth < lag(depth) over (partition by village order by date asc) then 1
          else 0
        as flag
FROM the_table