Time difference among end dates of first records t

问题:

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         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

回答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

回答2:

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