Re: compare date in two rows

Поиск
Список
Период
Сортировка
От Oliveiros d'Azevedo Cristina
Тема Re: compare date in two rows
Дата
Msg-id A64621AA38EF4B11BDE651E7DD157315@marktestcr.marktest.pt
обсуждение исходный текст
Ответ на compare date in two rows  (LALIT KUMAR <lalit.jss@gmail.com>)
Список 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 -----
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.


В списке pgsql-novice по дате отправления: