1

I'm having difficulty getting data from a query using the LAG method. The issue is that I only need one record from the entire set pulled down, but when I filter out that record in the WHERE clause it prevents the LAG method from using the entire dataset for its comparisons, and just returns NULL. For example, I have this set of records in my DB:

Dataset

All I want is the previous status to "Finished". But when I run this query, it only returns NULL because there is only one record the LAG method is working with:

SELECT
    s.StatusName, LAG(s.StatusName) OVER (ORDER BY cs.CustomerId, cs.CustomerStatusId) as PreviousStatus
FROM 
    #CustomerStatus as cs
    left join #Status as s on s.StatusId = cs.StatusId 
WHERE 
    cs.StatusId = 3 

ORDER BY
    cs.CustomerStatusId

If I remove the WHERE clause, it pulls the dataset below correctly, but I only want the third record:

Dataset2

Is there a way for me to grab only that one record from the results?

If you would like to work with my sample data, here is the Rexter link:

Rexter - LAG

  • What if there are two rows with status = 'Finished'? What should be returned then? – ypercubeᵀᴹ Feb 12 at 20:37
  • Is your question about returning this result (Finished) for every CustomerID? That is, the Query really is returning all the customers that have finished “jobs” – clifton_h Feb 13 at 5:00
2

It is happening because of the row being filtered before the LAG() function executes, making it unable to get the previous results.

To work around this, we could use a CTE or a subquery to execute the LAG() function before applying the filter.

Using a CTE

;WITH CTE  AS
(
SELECT
    s.StatusName, LAG(s.StatusName) OVER (ORDER BY cs.CustomerId, cs.CustomerStatusId) as PreviousStatus, cs.StatusId,cs.CustomerStatusId
FROM 
    #CustomerStatus as cs
    left join #Status as s on s.StatusId = cs.StatusId 
) 
SELECT StatusName ,PreviousStatus
FROM CTE
WHERE 
StatusId = 3 
ORDER BY
    CustomerStatusId;

Result

StatusName  PreviousStatus
Finished    Started

Rextester

With a subquery

SELECT outerquery.StatusName ,outerquery.PreviousStatus
FROM (    
    SELECT
    s.StatusName, LAG(s.StatusName) OVER (ORDER BY cs.CustomerId, cs.CustomerStatusId) as PreviousStatus, cs.StatusId,cs.CustomerStatusId
    FROM 
    #CustomerStatus as cs
    left join #Status as s on s.StatusId = cs.StatusId 
     ) as outerquery
WHERE 
outerquery.StatusId = 3 
ORDER BY
    outerquery.CustomerStatusId;

Result

StatusName  PreviousStatus
Finished    Started

Rextester

The ORDER BY CustomerStatusId; might not be needed if you only need one record

  • Is there any way to use this as a subquery in a larger query? – Vandel212 Feb 12 at 21:17
  • @Vandel212 I added a subquery to the answer, is that what you needed? – Randi Vertongen Feb 13 at 11:29
  • 1
    Thank you, I'll definitely look into it, and let you know. Some other things came up, and I had to put this on the back burner for now. Again, thank you for your help. – Vandel212 Feb 14 at 16:21
  • @Vandelt not a problem, looking forward to hearing if it worked :). – Randi Vertongen Feb 14 at 17:47
  • @Vandel212 awesome, glad it helped! – Randi Vertongen Feb 20 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.