Обсуждение: 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.
Does your table has a primary key, or does it have just those three columns?
----- Original Message -----From: LALIT KUMARSent: Friday, June 17, 2011 11:17 AMSubject: [NOVICE] compare date in two rowsHi,I have a table with following sample data.Village_name Date LabelA 1991-01-31 0A 1991-04-28 0A 1991-07-15 0B 2000-02-15 0B 2000-04-13 0B 2001-09-13 0C 1995-07-12 0C 1995-10-13 0I 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"
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
) umq
WHERE umq."Date" = yourTable."Date"
AND umq."Village_name" = yourTable."Village_name"
AND
yourTable."Date" - umq.anterior > 120
----- Original Message -----From: LALIT KUMARSent: Friday, June 17, 2011 11:17 AMSubject: [NOVICE] compare date in two rowsHi,I have a table with following sample data.Village_name Date LabelA 1991-01-31 0A 1991-04-28 0A 1991-07-15 0B 2000-02-15 0B 2000-04-13 0B 2001-09-13 0C 1995-07-12 0C 1995-10-13 0I 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.