Calculating elapsed time between actions within tasks


http://social.msdn.microsoft.com/Forums/sqlserver/en-US/beece289-b2fb-40ef-bf69-79a6784366fd/calculating-elapsed-time-between-actions-within-tasks

--Sample table and from RSingh, thanks.
DECLARE @TEMP TABLE(TaskID INT,ActionID INT,[User] INT,ActionDateAndTme DATETIME)
INSERT INTO @TEMP VALUES(1,11,1,'2008-07-02 08:45:00')
INSERT INTO @TEMP VALUES(1,12,1,'2008-07-02 08:50:00')
INSERT INTO @TEMP VALUES(1,13,2,'2008-08-02 09:45:00')
INSERT INTO @TEMP VALUES(2,11,1,'2008-10-02 08:45:00')
INSERT INTO @TEMP VALUES(2,12,1,'2008-11-02 08:45:00')
INSERT INTO @TEMP VALUES(2,13,2,'2008-11-02 08:50:00')</pre>
;
;with mycte as(select TaskID,ActionID,[User],ActionDateAndTme
,lag(ActionDateAndTme)
 Over(partition by TaskID Order by ActionID) preDT from @TEMP)
 , mycte1 as (Select TaskID,ActionID,[User]
 , datediff(s,preDT,ActionDateAndTme) as delta,
 preDT,ActionDateAndTme
  from mycte)
 Select TaskID,ActionID,[User]
 , Coalesce(RIGHT('0' + CONVERT(varchar(6), delta/86400),2)
 + ':' + RIGHT('0' + CONVERT(varchar(6), delta % 86400 / 3600), 2)
 + ':' + RIGHT('0' + CONVERT(varchar(2), (delta % 3600) / 60), 2)
 + ':' + RIGHT('0' + CONVERT(varchar(2), delta % 60), 2),'0') as [delta dd:hh:mm:ss]

  --For above format:http://adamhutson.com/2010/02/17/seconds-into-ddhhmmss-format/
--I have found an easy way to format
 , Coalesce(RIGHT('0' +Convert(varchar(10),DateDiff(day,'1900-01-01',(ActionDateAndTme-preDT))),2)  + ':'
 + Convert(varchar(10),(ActionDateAndTme-preDT), 108),'00:00:00:00') as [delta dd:hh:mm:ss--easy]

from mycte1 Order By TaskID,ActionID

For format datetime difference in dd:hh:mm:mm, please see another post:
https://jingyangli.wordpress.com/2013/08/29/format-two-datetime-diffrence-in-days-in-variable-length-ddhhmmss-t-sql/

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s