Substract date from previous row by group SQL query

Question

Data

id      date        
2380    10/30/12 09:00:00 
2380    10/30/12 09:05:00   
2380    10/30/12 09:10:00   
2380    10/30/12 09:15:00    
2381    10/30/12 10:00:00   
2381    10/30/12 10:05:00  
2381    10/30/12 10:10:00   
2381    10/30/12 10:15:00   
2382    10/30/12 11:00:00
2382    10/30/12 11:05:00
2382    10/30/12 10:10:00
2382    10/30/12 10:15:00

and I want the following solution

id      date                 duration        
2380    10/30/12 09:00:00    00:00:00 
2380    10/30/12 09:05:00    00:05:00   
2380    10/30/12 09:10:00    00:10:00
2380    10/30/12 09:15:00    00:15:00
2381    10/30/12 10:00:00    00:00:00
2381    10/30/12 10:05:00    00:05:00
2381    10/30/12 10:10:00    00:10:00
2381    10/30/12 10:15:00    00:15:00
2382    10/30/12 11:00:00    00:00:00
2382    10/30/12 11:05:00    00:05:00
2382    10/30/12 10:10:00    00:10:00
2382    10/30/12 10:15:00    00:10:00

I have tried to understand the logic behind the following thread but it's difficult to understand.

Substract date from previous row by group (using R)

select id, date, date - (select min(date) from date group by id) as duration 
from date

Closest I have got is for one id.

Have you tried LAG function? – Kelevra Oct 02 '19 at 21:54 — Kelevra, Oct 02 '19 at 21:54
No. Could you please let me know how to use? Thanks – Muffin13 Oct 03 '19 at 07:24 — Muffin13, Oct 03 '19 at 07:24

Vikrant More · Answer 1 · 2019-10-04T00:20:38.163

Try this below example hope this is what you are looking as output,

declare @t1 table
(
    id int,
    dtdate datetime
)

insert into @t1 values(2380,'10/30/12 09:00:00') 
insert into @t1 values(2380,'10/30/12 09:05:00')   
insert into @t1 values(2380,'10/30/12 09:10:00')   
insert into @t1 values(2380,'10/30/12 09:15:00')    
insert into @t1 values(2381,'10/30/12 10:00:00')   
insert into @t1 values(2381,'10/30/12 10:05:00')  
insert into @t1 values(2381,'10/30/12 10:10:00')   
insert into @t1 values(2381,'10/30/12 10:15:00')   
insert into @t1 values(2382,'10/30/12 11:00:00')
insert into @t1 values(2382,'10/30/12 11:05:00')
insert into @t1 values(2382,'10/30/12 10:10:00')
insert into @t1 values(2382,'10/30/12 10:15:00')

;WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (partition by id ORDER BY id,dtDate),
id,dtDate
FROM @t1 p
)
SELECT
a.id,
a.dtDate,
CASE WHEN prev.dtdate is NULL THEN '00:00:00' ELSE convert(nvarchar(8),a.dtdate- prev.dtdate,108) END as duration 
FROM CTE a
LEFT JOIN CTE prev ON a.id = prev.id AND prev.rownum = a.rownum - 1

Thank you but I get this error saying that LAG is recognized as a built-in function. Any other way? — Muffin13, Oct 03 '19 at 15:38
@Muffin13 - Edited code without LAG function, since LAG is introduced in SQL Server 2012 and on-wards — Vikrant More, Oct 04 '19 at 00:21

Isaac · Accepted Answer · 2019-10-03T16:48:16.680

The key to my approach is to find the minimum date value for each id which I refer to as the ReferenceDate. Then I join the main table to that and do the date math with the DATEDIFF() function and transform the results to hh:mi:ss with the CONVERT() function using style 108. Here is the dbfiddle.

IF OBJECT_ID('tempdb.dbo.#MyTable', 'U') IS NOT NULL
    DROP TABLE #MyTable;

CREATE TABLE #MyTable
(
    id INTEGER NOT NULL
  , date DATETIME NOT NULL
);
INSERT INTO #MyTable (id, date) VALUES (2380, '10/30/12 09:00:00');
INSERT INTO #MyTable (id, date) VALUES (2380, '10/30/12 09:05:00');
INSERT INTO #MyTable (id, date) VALUES (2380, '10/30/12 09:10:00');
INSERT INTO #MyTable (id, date) VALUES (2380, '10/30/12 09:15:00');
INSERT INTO #MyTable (id, date) VALUES (2381, '10/30/12 10:00:00');
INSERT INTO #MyTable (id, date) VALUES (2381, '10/30/12 10:05:00');
INSERT INTO #MyTable (id, date) VALUES (2381, '10/30/12 10:10:00');
INSERT INTO #MyTable (id, date) VALUES (2381, '10/30/12 10:15:00');
INSERT INTO #MyTable (id, date) VALUES (2382, '10/30/12 11:00:00');
INSERT INTO #MyTable (id, date) VALUES (2382, '10/30/12 11:05:00');
INSERT INTO #MyTable (id, date) VALUES (2382, '10/30/12 10:10:00');
INSERT INTO #MyTable (id, date) VALUES (2382, '10/30/12 10:15:00');
INSERT INTO #MyTable (id, date) VALUES (2382, '10/30/12 12:15:00');
INSERT INTO #MyTable (id, date) VALUES (2382, '10/30/12 10:15:30');

SELECT     a.*
         , CONVERT(NVARCHAR(8), a.date - b.ReferenceDate, 108) AS duration
FROM       #MyTable AS a
INNER JOIN (
    SELECT id, MIN(date) AS ReferenceDate 
    FROM #MyTable GROUP BY id) AS b ON a.id = b.id;

I have not tested putting my new answer in a view, but with no ORDER BY I believe it should work. — Isaac, Oct 03 '19 at 16:59
It did work, great, thanks a lot. I wanted to share a picture with you so I have started following you on twitter. Hope to send you there when you accept my request there. Have a great day! — Muffin13, Oct 03 '19 at 17:37

Substract date from previous row by group SQL query

2 Answers2