CODEWITHSUNDEEP
t-sqlsql-server-2012
Chris
Chris

Reputation: 1027

Calculate duration between previous row

I've got a SQL Server 2012 table with the following data:

Id; InstanceNo; ActionDate;              Text
-----------------------------------------------
1;  27500;      2016-04-15 13:39:11.843; Start
2;  27500;      2016-04-15 13:40:12.857; Step1
3;  27500;      2016-04-15 13:41:13.856; Step2
4;  27500;      2016-04-15 13:43:17.657; Step3
5;  27500;      2016-04-15 13:45:18.257; End
6;  29321;      2016-04-14 12:32:12.857; Start
7;  29321;      2016-04-14 12:40:17.247; Step1
8;  29321;      2016-04-15 09:18:19.156; End

Id is identity and table is ordered by InstanceNo, ActionDate.

I would like to calculate duration betweeneach line.

The problem is, each time there a new instance No, the start value should be '0' (and not the duration between Id 5 and Id 6 in the exemple)

Actually, I'm using this query. It is slow (only 25000 rows in the table) and did not start from zero when it is a new instance :

SELECT 
    t1.ID, t1.InstanceNo, t1.ActionDate, t1.Text, 
    DATEDIFF(ss, x.ActionDate, t1.ActionDate) AS [Duration sec]
FROM 
    Tmp_Stat_Sepa t1
OUTER APPLY 
    (SELECT TOP 1 ActionDate 
     FROM Tmp_Stat_Sepa t2 
     WHERE t2.ID < t1.ID 
     ORDER BY t2.ID DESC) x

Upvotes: 1

Views: 80

Answers (1)

Liesel
Liesel

Reputation: 2979

You dont mention which units you want the difference in so I've used seconds.

CREATE TABLE #Test (
  Id INTEGER,
  InstanceNo INTEGER,
  ActionDate DATETIME,
  Text VARCHAR(10)
);

INSERT INTO #Test (Id, InstanceNo, ActionDate, Text) VALUES
  (1,  27500,      '2016-04-15 13:39:11.843', 'Start'),
  (2,  27500,      '2016-04-15 13:40:12.857', 'Step1'),
  (3,  27500,      '2016-04-15 13:41:13.856', 'Step2'),
  (4,  27500,      '2016-04-15 13:43:17.657', 'Step3'),
  (5,  27500,      '2016-04-15 13:45:18.257', 'End'),
  (6,  29321,      '2016-04-14 12:32:12.857', 'Start'),
  (7,  29321,      '2016-04-14 12:40:17.247', 'Step1'),
  (8,  29321,      '2016-04-15 09:18:19.156', 'End');


SELECT A.Id
      ,A.InstanceNo
      ,A.ActionDate
      ,A.Text
      ,COALESCE(DATEDIFF(SECOND, LastActionDate, ActionDate), 0) DiffInSeconds
FROM (
SELECT Id
      ,InstanceNo
      ,ActionDate
      ,Text
      ,LAG(ActionDate) OVER (PARTITION BY InstanceNo ORDER BY InstanceNo, ActionDate) LastActionDate 
      FROM #Test ) A


Id          InstanceNo  ActionDate              Text       DiffInSeconds
----------- ----------- ----------------------- ---------- -------------
1           27500       2016-04-15 13:39:11.843 Start      0
2           27500       2016-04-15 13:40:12.857 Step1      61
3           27500       2016-04-15 13:41:13.857 Step2      61
4           27500       2016-04-15 13:43:17.657 Step3      124
5           27500       2016-04-15 13:45:18.257 End        121
6           29321       2016-04-14 12:32:12.857 Start      0
7           29321       2016-04-14 12:40:17.247 Step1      485
8           29321       2016-04-15 09:18:19.157 End        74282

(8 row(s) affected)

Upvotes: 2

Related Questions