CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
ParveenArora
ParveenArora

Reputation: 751

Find a time difference to the following row

How to find the time difference in minutes among different columns with different source IDs

No SourceID  RecordID  Start Date               End Date
1  1         1         2009-09-07 09:12:00.0000 2009-09-07 11:00:00.0000 
2  1         1         2009-09-07 11:19:00.0000 2009-09-07 12:12:00.0000  
3  1         1         2009-09-07 12:23:00.0000 2009-09-07 12:54:00.0000  
4  1         1         2009-09-07 13:49:00.0000 2009-09-07 14:45:00.0000 
5  2         2         2009-10-12 09:12:00.0000 2009-10-12 11:00:00.0000  
6  2         2         2009-10-12 11:19:00.0000 2009-10-12 12:12:00.0000
7  2         2         2009-10-12 12:23:00.0000 2009-10-12 12:54:00.0000

How to get the difference between EndDate of first record with Start Date of next record and same for different source ID and how to fill the empty space with "-1" and then update into a column?

here result needs get as

 SourceID  RecordID  Start Date               End Date                 DiffMin
 1         1         2009-09-07 09:12:00.0000 2009-09-07 11:00:00.0000 19 
 1         1         2009-09-07 11:19:00.0000 2009-09-07 12:12:00.0000 11 
 1         1         2009-09-07 12:23:00.0000 2009-09-07 12:54:00.0000 55 
 1         1         2009-09-07 13:49:00.0000 2009-09-07 14:45:00.0000 NULL
 2         2         2009-10-12 09:12:00.0000 2009-10-12 11:00:00.0000 19 
 2         2         2009-10-12 11:19:00.0000 2009-10-12 12:12:00.0000 11
 2         2         2009-10-12 12:23:00.0000 2009-10-12 12:54:00.0000 55

Upvotes: 1

Views: 70

Answers (1)

Glenn
Glenn

Reputation: 9150

Probably want to use an analytic function along these lines:

postgres=# SELECT No, SourceID, RecordID, StartDate, EndDate
postgres-#       ,LEAD(StartDate) OVER(PARTITION BY SourceID ORDER BY StartDate) - EndDate AS diff
postgres-#   FROM myTable;
 no | sourceid | recordid |      startdate      |       enddate       |   diff
----+----------+----------+---------------------+---------------------+-----------
  1 |        1 |        1 | 2009-09-07 09:12:00 | 2009-09-07 11:00:00 |  00:19:00
  2 |        1 |        1 | 2009-09-07 11:19:00 | 2009-09-07 12:12:00 |  00:11:00
  3 |        1 |        1 | 2009-09-07 12:23:00 | 2009-09-07 12:54:00 |  00:55:00
  4 |        1 |        1 | 2009-09-07 13:49:00 | 2009-09-07 14:45:00 |
  5 |        2 |        2 | 2009-10-12 09:12:00 | 2009-10-12 11:00:00 |  00:19:00
  6 |        2 |        2 | 2009-10-12 11:19:00 | 2009-10-12 12:12:00 |  00:11:00
  7 |        2 |        2 | 2009-10-12 12:23:00 | 2009-10-12 12:54:00 |
(7 rows)

Upvotes: 4

Related Questions