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: (view raw, whole thread or download thread mbox)
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.


UPDATE yourTable
SET "Label" = 1
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" 
yourTable."Date" - umq.anterior > 120
  ----- Original Message ----- 
  To: pgsql-novice(at)postgresql(dot)org 
  Sent: Friday, June 17, 2011 11:17 AM
  Subject: [NOVICE] compare date in two rows

  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.

  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-2017 The PostgreSQL Global Development Group