Thread: Compare dates

Hi,
I have following table data:

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.

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