Skip site navigation (1) Skip section navigation (2)

Re: compare date in two rows

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 (view raw or flat)
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.



In response to

pgsql-novice by date

Next:From: JORGE MALDONADODate: 2011-06-17 17:17:53
Subject: Date formatting function
Previous:From: James David SmithDate: 2011-06-17 11:11:52
Subject: Extract from date field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group