SQLTeam.com | Weblogs | Forums

How to get date difference between two rows based on one column value in SQL Server?


#1

I have a result set which returns results like this,

ID     Q_Number    Q_CycleID     INC_Date1       INC_Date2             OUT_Date1        OUT_Date2

8322445  Q8322445   17921439    2014-03-06 00:00:00  2014-05-14 13:31:00    NULL          NULL
8322445  Q8322445   17614525        NULL            NULL           2014-05-16 09:22:00  2014-05-20 07:29:00

I want to get the date difference between all the columns. It works for difference between INC_Date 2 and INC_Date1, OUT_Date2 and OUT_Date1 but it does not work for OUT_Date1 - INC_Date2.

How can I do this?


#2

In the given result set OUT_Date1 and INC_Date2 both are not having value in same row.
Please correct me if my understanding is wrong


#3

Yes, there are two cycles for each Question so they are never in the same row.


#4

you can use LEAD/LAG functions (SQL Server 2012 and up). Easy and fast. Otherwise you need a self-join to do inter-row calculations.


#5

I am using SQL Server 2008 so can't use LAG and LEAD.


#6

OK -- so self-join is the way to go