SQLTeam.com | Weblogs | Forums

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

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?

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

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

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.

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

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