开发者

Time difference among end dates of first records to next record first dates

开发者 https://www.devze.com 2023-03-25 20:45 出处:网络
How to find the dates difference in minutes among different columns levels as No SourceIDRecordIDStart DateEnd Date

How to find the dates difference in minutes among different columns levels as

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 

How to get the difference between EndDate of first record with Start Date of next record and the last record with NULL value.

here result needs get as

 SourceID  RecordID  Start Date               End Date                 DiffMin
 1         1     开发者_如何学C    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

Thanks

Prav


SELECT  SourceId,RecordId,StartDate,EndDate
   , DATEDIFF(mi,a.EndDate,b.StartDate) DiffMin
FROM table a
LEFT JOIN table b ON A.no = B.no+1


SELECT SourceId,
       RecordId,
       StartDate,
       EndDate,
       DATEDIFF(mi,a.EndDate,b.StartDate) DiffMin
FROM table a
LEFT JOIN table b ON A.no = B.no-1
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号