Date Difference between consecutive rows SQL

Question

Hi Team,

How to achieve bellow requirement.

Answer 1

    SELECT c1.*,DATEDIFF(mi,c2.createdon,c1.createdon) [date diff in min] 
    FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY [seat number])rr FROM #test) c1
    JOIN (SELECT *,ROW_NUMBER() OVER(ORDER BY [seat number])rr FROM #test) c2 
    ON c1.rr=c2.rr-1

Answer 2

Please refer to:

CREATE TABLE #test([seat number] CHAR(15),[Bus number] INT,
person CHAR(15),createdon DATETIME,details CHAR(55))
INSERT INTO #test VALUES('B12',12345,'irfan','2021-4-19 00:14:08','re'),
                        ('B12',12345,'support','2021-4-18 21:30:14','qu')

;WITH cte
as(SELECT *,ROW_NUMBER() OVER(ORDER BY [seat number])rr FROM #test)

SELECT c1.*,DATEDIFF(mi,c2.createdon,c1.createdon) [date diff in min] FROM cte c1
JOIN cte c2 ON c1.rr=c2.rr-1

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Thanks for your update.
Is their any query without using CTE ?

Answer 4

You have to have some way of filtering out the 'first' row. If you cannot use a CTE (not sure why) then you can use a derived table. Using the data provided by @Echo :

DROP TABLE IF EXISTS #test;  
CREATE TABLE #test([seat number] CHAR(15),[Bus number] INT,  
 person CHAR(15),createdon DATETIME,details CHAR(55))  
 INSERT INTO #test VALUES('B12',12345,'irfan','2021-4-19 00:14:08','re'),  
                         ('B12',12345,'support','2021-4-18 21:30:14','qu');  
  
 Select *  
   From (  
 Select *  
      , DateDiffInMin = datediff(minute, lag(t.CreatedOn) over(Partition By t.[seat number], t.[Bus number]  
                                                                  Order By t.createdon), t.createdon)  
   From #test           t  
        ) As d  
  Where d.DateDiffInMin Is Not Null;  

If you have more than 2 rows per seat/bus - you will get the difference for all but the first row.