1

I'm having a difficult time wrapping my head around this issue. I need to get the previous status of our finished customers. I learned about the LAG function and it almost does what I need, but the issue is it can potentially get the status from a different customer because the previous row is a different CustomerId.

Is there a way to limit the LAG function to only return a value of null if the previous CustomerId is different or something that will generate the same affect? I created some dummy data here: Rexter Lag Testing

As you can see, the 4th row is for customerId 2, but PreviousStatus value is from CustomerId 1. It should be NULL.

Lag Testing Screenshot

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

fiddle

  • 2
    The ORDER BY should be by a different column/expression (perhaps CustomerStatusID), otherwise the order will be undefined. +1 for the PARTITION BY. – ypercubeᵀᴹ Jan 31 '19 at 18:05

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.