Select one record from LAG Query

Question

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:

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:

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

Answer 1

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