MSSQL Calculate Date Difference in same column-CodePudding

My data is as follows:

ID  DATE
1   2014-02-01 09:00:00.00
1   2014-03-01 11:00:00.00
1   2014-01-01 11:00:00.00
2   2010-12-01 05:00:00.00
2   2011-10-01 03:30:00.00
2   2012-09-01 02:40:00.00

I need to calculate the date difference(day or hours difference is fine) between each row. The first row can be 0. I have tried:

date - coalesce(lag(date) over(order by id, date) as day_difference

daydiff(date - coalesce(lag(date) over(order by id, date)) as day_difference

both variants are not working(gives incorrect syntax error).

Please help.

CodePudding user response：

You were close:

datediff(day, date, coalesce(lag(date) over(order by id, date)))  as day_difference

But also, you might want to partition by the ID:

datediff(day, date, coalesce(lag(date) over(partition by ID order by id, date)))  as day_difference