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  
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,914 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    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  
      
    
    1 person found this answer helpful.

  2. CosmogHong-MSFT 23,946 Reputation points Microsoft Vendor
    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.


  3. Tom Phillips 17,716 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'  
      
    

  4. Tom Cooper 8,466 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

    0 comments No comments

  5. Jingyang Li 5,891 Reputation points
    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  
    
    0 comments No comments