Jerley Garcia Jerley Garcia - 3 months ago 13
SQL Question

How to use DateDiff into only one SELECT statement?

I want to make a short version on my DATEDIFF function on my SQL Query. In my code, I created two temporary tables then there, I select and use the DATEDIFF funtion.

I would want this code to be simplified and only use ONE SELECT statement that will provide the same results. Is it possible?

Here is my result:

enter image description here

This is my SQL Query

DECLARE @Temp TABLE (ID int, Stamp datetime)

INSERT INTO @Temp (ID, Stamp) VALUES (1, '2016-08-17')
INSERT INTO @Temp (ID, Stamp) VALUES (1, GETDATE())
INSERT INTO @Temp (ID, Stamp) VALUES (1, GETDATE()+0.5)
INSERT INTO @Temp (ID, Stamp) VALUES (2, '2016-08-16')
INSERT INTO @Temp (ID, Stamp) VALUES (2, GETDATE())
INSERT INTO @Temp (ID, Stamp) VALUES (2, GETDATE()+3)

SELECT ROW_NUMBER() OVER (ORDER BY ID) as c, ID, Stamp INTO #Temp2
FROM @Temp

SELECT ROW_NUMBER() OVER (ORDER BY ID) as d, ID, Stamp INTO #Temp3
FROM @Temp

SELECT temp2.ID, temp2.Stamp, ISNULL(DATEDIFF(day, temp3.Stamp, temp2.Stamp),0) as DateDiff
FROM #Temp2 as temp2
LEFT JOIN #Temp3 as temp3 on temp2.ID = temp3.ID and temp2.c = temp3.d + 1


Thanks!

Answer

If you are using SQL Server 2012:

select * ,isnull(datediff(day,lag(stamp) over(partition by id order by stamp),stamp) ,0) 
from @temp t1

Else use this..

;with cte
as
(select * ,row_number() over (partition by id order by stamp ) as rownum
from @temp t1
)
select c1.id,c1.stamp,isnull(datediff(day,c2.stamp,c1.stamp),0) as datee
 from cte c1
left join
cte c2
on c1.id=c2.id and c1.rownum=c2.rownum+1