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
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.