Hi Team,
How to achieve bellow requirement.
Hi Team,
How to achieve bellow requirement.
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
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.
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.
12 people are following this question.