Search code examples
sqlsql-serverwindow-functions

LEAD and LAG in SQL Server

I am trying to identify if a template has been applied after saving a ticket. The code does run, but it does not capture the strings "template applied" from the ACTION column.

This is table where I'm getting the data:

enter image description here

Here is the results I am getting:

enter image description here

My code:

SELECT *
FROM
    (SELECT              
         [ACTION], 
         CASE
            WHEN ACTION = 'Save' AND LAG(ACTION) OVER (ORDER BY SYS_CREATED_BY, SYS_CREATED_ON) LIKE 'Template Applied%' AND LAG(SYS_CREATED_BY) OVER (ORDER BY SYS_CREATED_BY, SYS_CREATED_ON) = SYS_CREATED_BY 
               THEN 1 
               ELSE 0 
         END AS RELEVANT_SAVE, 
         [TICKET_NUMBER], 
         [USER_ID], 
         [SYS_CREATED_BY], [SYS_CREATED_ON], 
         DATEPART(YEAR, [SYS_CREATED_ON]) AS YEAR, 
         DATEPART(MONTH, [SYS_CREATED_ON]) AS month
     FROM            
         TICKETSDB
     WHERE        
         (ACTION = 'Save' OR ACTION LIKE 'Template Applied%')) AS T
Solution

  • It seems like you need to move part of the order by to partition by

    In both lead and lag statements change the over clause to

    OVER (PARTITION BY SYS_CREATED_BY ORDER BY SYS_CREATED_ON DESC)

    This will fix where you want to view it by person.

    Or remove it completely if you don’t care 

     OVER ( ORDER BY SYS_CREATED_ON)