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 shoul be 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