Hi,
I have a table with following sample 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 1991-09-23 1
W001 Akoli 1992-01-31 2.9
W001 Akoli 1992-03-31 4
W001 Akoli 1992-05-31 4.35
W001 Akoli 1992-10-31 2
W0002 Awale 1977-05-30 7.2
W0002 Awale 1978-05-30 7.2
W0002 Awale 1977-10-30 4.22
W003 Talwade 1998-01-23 8
W003 Talwade 1998-03-23 9.2
W003 Talwade 1998-06-09 8.95
W003 Talwade 1998-10-30 1.25
W003 Talwade 1999-01-16 0.5
The fileds (site_id,date) uniquely identifies a tuple.
The date is related to season of rain.
For each village the following rules need to be there.
A ) The depth in a year from month 01 to 05 increases as for akoli
1991-01 : 3.5
1991-03 : 3.7
1991-05: 4.35
B ) The depth in a year from month 06 to 10 must decrease as compared to previous date depth.
1991-09 : 1 when compared to 4.35 of 1991-05
If there was reading in 1991-06-,1991-08 then 06 would be compared to 05 and 08 would have been compared to 06.
C) The depth in a year from month 11 to 12 must increase as compared to previous date depth.
Village Akoli has no reading but other villages may have.
Now for next year 1992 the first reading will be compared with last year (1991) last reading and it should be more .
1992-01: 2.9
Now the depth which violates these rules i need to set the flag field as 1.
The last tuple is an example of violation of the rule.
There are no fixed months in which depth is measured.
No fixed number of readings in a month
No fixed starting and ending year.