compare date in two rows

Does your table has a primary key, or does it have just those three columns?
----- Original Message -----
From: LALIT KUMAR
To: [email protected]
Sent: Friday, June 17, 2011 11:17 AM
Subject: [NOVICE] compare date in two rows


Hi,
I have a table with following sample data.


Village_name Date Label


A 1991-01-31 0


A 1991-04-28 0


A 1991-07-15 0


B 2000-02-15 0


B 2000-04-13 0


B 2001-09-13 0


C 1995-07-12 0


C 1995-10-13 0


I need to update the label to 1 when the number of days between two dates(when compared with previous date) is greater than 120 for each village.


Illustration:
For village A: row 1 date will not be compared to any date.
row 2 date will be compared to row 1 date and if satisfies the condition label will be made 1.
row 3 compare to row 2 and continue this till last row for village A.


Similarly first date for village B,C.

Hi, Kumar.

4get bout my previous question it is not really necessary.
Try this out and tell me if it solved your problem.

Best,
Oliveiros

UPDATE yourTable
SET "Label" = 1
FROM (
SELECT x."Date" as "Date",x."Village_name" as "Village_name",MAX(y."Date") as anterior
FROM yourTable x
JOIN yourTable y
ON y."Village_name" = x."Village_name"
AND y."Date" < x."Date"
GROUP BY x."Date",x."Village_name"
) umq
WHERE umq."Date" = yourTable."Date"
AND umq."Village_name" = yourTable."Village_name"
AND
yourTable."Date" - umq.anterior > 120
----- Original Message -----
From: LALIT KUMAR
To: [email protected]
Sent: Friday, June 17, 2011 11:17 AM
Subject: [NOVICE] compare date in two rows


Hi,
I have a table with following sample data.


Village_name Date Label


A 1991-01-31 0


A 1991-04-28 0


A 1991-07-15 0


B 2000-02-15 0


B 2000-04-13 0


B 2001-09-13 0


C 1995-07-12 0


C 1995-10-13 0


I need to update the label to 1 when the number of days between two dates(when compared with previous date) is greater than 120 for each village.


Illustration:
For village A: row 1 date will not be compared to any date.
row 2 date will be compared to row 1 date and if satisfies the condition label will be made 1.
row 3 compare to row 2 and continue this till last row for village A.


Similarly first date for village B,C.