CODEWITHSUNDEEP
sqlsql-serverwindow-functions
Jonathan
Jonathan

Reputation: 172

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

Upvotes: 0

Views: 924

Answers (1)

S3S
S3S

Reputation: 25112

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)

Upvotes: 1

Related Questions