question

balanarasimhac-1495 avatar image
0 Votes"
balanarasimhac-1495 asked EchoLiu-msft commented

Date Difference between consecutive rows SQL

Hi Team,

How to achieve bellow requirement.
89805-capture20210421124321811.png


sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you provide any updates?

Echo

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered
     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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

balanarasimhac-1495 avatar image
0 Votes"
balanarasimhac-1495 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.