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: