Share via

Lead / Lag Use - T-SQL question

John K 186 Reputation points
2022-09-07T02:48:28.13+00:00

Hi,

I have a table in the following format. It has set of Actions and start and end date . There are multiple rows per UniqueAppNum.
I want one row per AppNum and columns for specific action and time taken for application to move from one Action to other.

Table is like this
238421-capture.png

Result is like this
238370-result.png

ACT2 column will have time elapsed between ACT1 Startdate and ACT2 Enddate
ACT4 column will have time elapsed between ACT2 Startdate and ACT4 EndDate
ACT5 column will have time elapsed between ACT4 Startdate and ACT5 EndDate

Application 2 has no ACT5 so the column is NULL. 

  DECLARE @LOCAL_TABLEVARIABLE TABLE  
	(UniqueAppNum VARCHAR(1000),   
	 ActionName VARCHAR(1000),   
	 StartDate Datetime,  
	 EndDate   Datetime,  
	 [StatusName] VARCHAR(100)  
	)  
	Insert into @LOCAL_TABLEVARIABLE  values  
	( 'APP1','ACT1','2022-03-27 00:10:15.000'  ,'2022-03-27 00:11:00.000'  ,'Init' ),  
	( 'APP1','ACT2','2022-03-27 00:11:01.000'  ,'2022-03-27 00:11:08.000' ,'ST2'),  
	( 'APP1','ACT3','2022-03-27 00:11:09.000'  ,'2022-03-27 00:11:15.000' ,'ST3'),  
	( 'APP1','ACT4','2022-03-27 00:11:16.000'  ,'2022-03-27 00:12:15.000' ,'Waiting'),  
	( 'APP1','ACT5','2022-03-27 00:12:15.000'  ,'2022-03-27 00:12:33.000' ,'Waiting'),  
  
	( 'APP2','ACT1','2022-03-28 00:10:15.000'  ,'2022-03-28 00:11:00.000'  ,'Init' ),  
	( 'APP2','ACT2','2022-03-28 00:11:01.000'  ,'2022-03-28 00:11:08.000' ,'ST2'),  
	( 'APP2','ACT3','2022-03-28 00:11:09.000'  ,'2022-03-28 00:11:15.000' ,'ST3'),  
	( 'APP2','ACT4','2022-03-28 00:11:16.000'  ,'2022-03-28 00:12:15.000' ,'Waiting')  
  
	SELECT * FROM @LOCAL_TABLEVARIABLE
Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jingyang Li 5,901 Reputation points Volunteer Moderator
    2022-09-07T17:28:31.273+00:00 
     DECLARE @LOCAL_TABLEVARIABLE TABLE  
     (UniqueAppNum VARCHAR(1000),   
      ActionName VARCHAR(1000),   
      StartDate Datetime,  
      EndDate   Datetime,  
      [StatusName] VARCHAR(100)  
     )  
     Insert into @LOCAL_TABLEVARIABLE  values  
     ( 'APP1','ACT1','2022-03-27 00:10:15.000'  ,'2022-03-27 00:11:00.000'  ,'Init' ),  
     ( 'APP1','ACT2','2022-03-27 00:11:01.000'  ,'2022-03-27 00:11:08.000' ,'ST2'),  
     ( 'APP1','ACT3','2022-03-27 00:11:09.000'  ,'2022-03-27 00:11:15.000' ,'ST3'),  
     ( 'APP1','ACT4','2022-03-27 00:11:16.000'  ,'2022-03-27 00:12:15.000' ,'Waiting'),  
     ( 'APP1','ACT5','2022-03-27 00:12:15.000'  ,'2022-03-27 00:12:33.000' ,'Waiting'),  
      
     ( 'APP2','ACT1','2022-03-28 00:10:15.000'  ,'2022-03-28 00:11:00.000'  ,'Init' ),  
     ( 'APP2','ACT2','2022-03-28 00:11:01.000'  ,'2022-03-28 00:11:08.000' ,'ST2'),  
     ( 'APP2','ACT3','2022-03-28 00:11:09.000'  ,'2022-03-28 00:11:15.000' ,'ST3'),  
     ( 'APP2','ACT4','2022-03-28 00:11:16.000'  ,'2022-03-28 00:12:15.000' ,'Waiting')  
      
     SELECT  UniqueAppNum,   
 	 Format(  
	 Max(case when ActionName='Act2' then EndDate else null end)   
	  -Max(case when ActionName='Act1' then StartDate else null end), 'HH:mm:ss') ACT2,  
	 Format(   
	 Max(case when ActionName='Act4' then EndDate else null end)  
	 -Max(case when ActionName='Act2' then StartDate else null end), 'HH:mm:ss') ACT4,  
      Format(   
	  Max(case when ActionName='Act5' then EndDate else null end)  
	  -Max(case when ActionName='Act4' then StartDate else null end), 'HH:mm:ss') ACT6   
  
FROM @LOCAL_TABLEVARIABLE  
GROUP BY UniqueAppNum

    Was this answer helpful?

    1 person found this answer helpful.
  2. Jingyang Li 5,901 Reputation points Volunteer Moderator
    2022-09-08T14:33:38.287+00:00 
      DECLARE @LOCAL_TABLEVARIABLE TABLE  
       (UniqueAppNum VARCHAR(1000),   
        ActionName VARCHAR(1000),   
        StartDate Datetime,  
        EndDate   Datetime,  
        [StatusName] VARCHAR(100)  
       )  
       Insert into @LOCAL_TABLEVARIABLE  values  
       ( 'APP1','ACT1','2022-03-27 00:10:15.000'  ,'2022-03-27 00:11:00.000'  ,'Init' ),  
       ( 'APP1','ACT2','2022-03-27 00:11:01.000'  ,'2022-03-27 00:11:08.000' ,'ST2'),  
       ( 'APP1','ACT3','2022-03-27 00:11:09.000'  ,'2022-03-27 00:11:15.000' ,'ST3'),  
       ( 'APP1','ACT4','2022-03-29 00:11:16.000'  ,'2022-03-29 00:12:15.000' ,'Waiting')  
              
       SELECT  UniqueAppNum,   
	   Coalesce( Convert(varchar(5),abs(DateDiff(day, ( Max(case when ActionName='Act2' then EndDate else null end)   
        -Max(case when ActionName='Act1' then StartDate else null end)),'1900-01-01'))) + ':'  
+ Convert(varchar(10),( Max(case when ActionName='Act2' then EndDate else null end)   
        -Max(case when ActionName='Act1' then StartDate else null end)), 108),'00:00:00:00') as [ACT2-days:hh:mm:ss]  
  
,Coalesce( Convert(varchar(5),abs(DateDiff(day, ( Max(case when ActionName='Act4' then EndDate else null end)  
       -Max(case when ActionName='Act2' then StartDate else null end)),'1900-01-01'))) + ':'  
+ Convert(varchar(10),( Max(case when ActionName='Act4' then EndDate else null end)  
       -Max(case when ActionName='Act2' then StartDate else null end)), 108),'00:00:00:00') as [ACT4-days:hh:mm:ss]  
          
  FROM @LOCAL_TABLEVARIABLE  
  GROUP BY UniqueAppNum

    Was this answer helpful?

    0 comments
  3. Tom Cooper 8,501 Reputation points
    2022-09-08T07:46:19.667+00:00

    Try 

    ;With cte As  
(SELECT act1.UniqueAppNum,   
   DateDiff(second, act1.StartDate, act2.EndDate) as ACT2,  
   DateDiff(second, act2.StartDate, act4.EndDate) as ACT4,  
   DateDiff(second, act4.StartDate, act5.EndDate) as ACT5  
FROM @LOCAL_TABLEVARIABLE act1  
   LEFT OUTER JOIN @LOCAL_TABLEVARIABLE act2  
       ON act1.UniqueAppNum = act2.UniqueAppNum  
       AND act2.ActionName = 'ACT2'  
   LEFT OUTER JOIN @LOCAL_TABLEVARIABLE act3  
       ON act1.UniqueAppNum = act3.UniqueAppNum  
       AND act3.ActionName = 'ACT3'  
   LEFT OUTER JOIN @LOCAL_TABLEVARIABLE act4  
       ON act1.UniqueAppNum = act4.UniqueAppNum  
       AND act4.ActionName = 'ACT4'  
   LEFT OUTER JOIN @LOCAL_TABLEVARIABLE act5  
       ON act1.UniqueAppNum = act5.UniqueAppNum  
       AND act5.ActionName = 'ACT5'  
WHERE act1.ActionName = 'ACT1')  
Select UniqueAppNum,  
 NullIf(Concat_WS(':', Format(ACT2 / (24*60*60), '0#'), Format((ACT2 % (24*60*60)) / (60*60), '0#'), Format((ACT2 % (60*60)) / 60, '0#'), Format(ACT2 % 60, '0#')), '') As ACT2,  
 NullIf(Concat_WS(':', Format(ACT4 / (24*60*60), '0#'), Format((ACT4 % (24*60*60)) / (60*60), '0#'), Format((ACT4 % (60*60)) / 60, '0#'), Format(ACT4 % 60, '0#')), '') As ACT4,  
 NullIf(Concat_WS(':', Format(ACT5 / (24*60*60), '0#'), Format((ACT5 % (24*60*60)) / (60*60), '0#'), Format((ACT5 % (60*60)) / 60, '0#'), Format(ACT5 % 60, '0#')), '') As ACT5  
From cte;

    Tom

    Was this answer helpful?

    0 comments
  4. Tom Phillips 17,786 Reputation points
    2022-09-07T13:45:05.433+00:00

    I find it more straight forward to do this via a self-join: 

         SELECT act1.UniqueAppNum,   
		CAST(act2.EndDate-act1.StartDate as TIME) as ACT2,  
		CAST(act4.EndDate-act2.StartDate as TIME) as ACT4,  
		CAST(act5.EndDate-act4.StartDate as TIME) as ACT5  
	 FROM @LOCAL_TABLEVARIABLE act1  
		LEFT OUTER JOIN @LOCAL_TABLEVARIABLE act2  
			ON act1.UniqueAppNum = act2.UniqueAppNum  
			AND act2.ActionName = 'ACT2'  
		LEFT OUTER JOIN @LOCAL_TABLEVARIABLE act3  
			ON act1.UniqueAppNum = act3.UniqueAppNum  
			AND act3.ActionName = 'ACT3'  
		LEFT OUTER JOIN @LOCAL_TABLEVARIABLE act4  
			ON act1.UniqueAppNum = act4.UniqueAppNum  
			AND act4.ActionName = 'ACT4'  
		LEFT OUTER JOIN @LOCAL_TABLEVARIABLE act5  
			ON act1.UniqueAppNum = act5.UniqueAppNum  
			AND act5.ActionName = 'ACT5'  
  
	 WHERE act1.ActionName = 'ACT1'

    Was this answer helpful?

  5. LiHongMSFT-4306 31,621 Reputation points
    2022-09-07T03:37:21.207+00:00

    Hi @John K
    Try this: 

    ;WITH CTE AS  
(  
 SELECT UniqueAppNum,ActionName+'-'+C.TAG AS New_Column,C.TIME    
 FROM @LOCAL_TABLEVARIABLE CROSS APPLY(VALUES(StartDate,'StartDate'),(EndDate,'EndDate'))C(TIME,TAG)  
)  
SELECT UniqueAppNum  
      ,CAST([ACT2-Enddate]-[ACT1-Startdate] AS TIME) AS ACT2  
	  ,CAST([ACT4-EndDate]-[ACT2-Startdate] AS TIME) AS ACT4  
	  ,CAST([ACT5-EndDate]-[ACT4-Startdate] AS TIME) AS ACT5  
FROM CTE  
PIVOT(MAX(TIME) FOR New_Column IN ([ACT1-Startdate],[ACT2-Enddate],[ACT2-Startdate],[ACT4-EndDate],[ACT4-Startdate],[ACT5-EndDate]))P

    Note: the result image you post doesn't match the logic you described, please check that.
    For example, ACT5 column will have time elapsed between ACT4 Startdate and ACT5 EndDate, which is '2022-03-27 00:12:33.000' - '2022-03-27 00:11:16.000' = '00:01:17.000'. But in the image, it is '00:00:18'

    Best regards,
    LiHong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.