October 15, 2014 at 3:16 pm
How can i calculate datediff (in minutes) between two consecutive rows without using CTE & Temp tables?
I was using this successfully, but i need something without CTE and temp tables (both of them are not supported in the tool i am trying to generate a report using custom query).
WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS RowNo, * FROM MyTable)
SELECT t1.*, ISNULL(DATEDIFF(mi, t2.CreateDate, t1.CreateDate), 0) AS Duration
FROM CTE t1
LEFT JOIN CTE t2 ON t1.RowNo = t2.RowNo - 1
ORDER BY t1.Id desc
October 15, 2014 at 3:59 pm
You can probably accomplish this using a self-join to your table.
It's tough to say much more than that without seeing the data with which you are working.
If you post DDL for table structure and sample data, and an example result set. I'd be happy to assist you further.
October 15, 2014 at 4:19 pm
October 15, 2014 at 4:19 pm
UDBA (10/15/2014)
How can i calculate datediff (in minutes) between two consecutive rows without using CTE & Temp tables?I was using this successfully, but i need something without CTE and temp tables (both of them are not supported in the tool i am trying to generate a report using custom query).
WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS RowNo, * FROM MyTable)SELECT t1.*, ISNULL(DATEDIFF(mi, t2.CreateDate, t1.CreateDate), 0) AS Duration
FROM CTE t1
LEFT JOIN CTE t2 ON t1.RowNo = t2.RowNo - 1
ORDER BY t1.Id desc
Have you tried putting the query above as the definition of a view and using the view as the source for your report?
October 15, 2014 at 4:50 pm
You could try the following
SELECT *, ISNULL(DATEDIFF(mi, LAG(CreateDate,1) OVER (ORDER BY ID), CreateDate), 0) AS Duration
FROM MyTable
Edit:
To refine it a bit more by using default and removing isnull
SELECT *, DATEDIFF(mi, LAG(CreateDate,1, CreateDate) OVER (ORDER BY ID), CreateDate) AS Duration
FROM MyTable
October 15, 2014 at 9:52 pm
Further on mickyT's post, the cross row referencing window functions are perfect for this kind of queries, here is a quick sample.
😎
USE tempdb;
GO
;WITH SAMPLE_DATA(SD_ID,SD_DATE) AS
( SELECT * FROM (VALUES
(1,'2014-01-02')
,(2,'2014-01-05')
,(3,'2014-01-08')
,(4,'2014-01-14')
,(5,'2014-01-25')
,(6,'2014-01-26')
,(7,'2014-01-31')
) AS X(SD_ID,SD_DATE)
)
SELECT
SD.SD_ID
,SD.SD_DATE
,LAG(SD.SD_DATE,1,SD.SD_DATE) OVER
(
ORDER BY SD.SD_ID
) AS LAG_SD_DATE
,LEAD(SD.SD_DATE,1,SD.SD_DATE) OVER
(
ORDER BY SD.SD_ID
) AS LEAD_SD_DATE
,DATEDIFF(DAY,LAG(SD.SD_DATE,1,SD.SD_DATE) OVER
(
ORDER BY SD.SD_ID
),SD.SD_DATE) AS DD_1
,DATEDIFF(DAY,SD.SD_DATE,LEAD(SD.SD_DATE,1,SD.SD_DATE) OVER
(
ORDER BY SD.SD_ID
)) AS DD_2
FROM SAMPLE_DATA SD;
Results
SD_ID SD_DATE LAG_SD_DATE LEAD_SD_DATE DD_1 DD_2
----------- ---------- ----------- ------------ ----------- -----------
1 2014-01-02 2014-01-02 2014-01-05 0 3
2 2014-01-05 2014-01-02 2014-01-08 3 3
3 2014-01-08 2014-01-05 2014-01-14 3 6
4 2014-01-14 2014-01-08 2014-01-25 6 11
5 2014-01-25 2014-01-14 2014-01-26 11 1
6 2014-01-26 2014-01-25 2014-01-31 1 5
7 2014-01-31 2014-01-26 2014-01-31 5 0
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply