From: | "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt> |
---|---|
To: | "LALIT KUMAR" <lalit(dot)jss(at)gmail(dot)com>, <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: compare date in two rows |
Date: | 2011-06-17 11:32:19 |
Message-ID: | A64621AA38EF4B11BDE651E7DD157315@marktestcr.marktest.pt |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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: pgsql-novice(at)postgresql(dot)org
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.
From | Date | Subject | |
---|---|---|---|
Next Message | JORGE MALDONADO | 2011-06-17 17:17:53 | Date formatting function |
Previous Message | James David Smith | 2011-06-17 11:11:52 | Extract from date field |