SQL - LAG but skip specific rows based on value?-CodePudding

I have the below table. What I am trying to do is add a LAG column which shows me the previous [EndDate] for ONLY entries that have a status that isn't 6. 6 means inactive.. so I don't want to count them.

SPID        ID            Status       StartDate                 EndDate   
29333       102             7         01 jan 2013              1 Jan 2014
29333       202             6         7 Jun 2021               15 Jun 2022
29333       384             6         10 jun 2021              17 jun 2022
29333       532             2         8 oct 2023               10 oct 2025

I have used a LAG function which works but not sure how I could amend this to skip the rows I don't need? My ideal output would look like this..

SPID        ID            Status       StartDate                 EndDate           PrevED
29333       102             7         01 jan 2013              1 Jan 2014        01-Jan-1900  
29333       202             6         7 Jun 2021               15 Jun 2022       1-Jan-2014
29333       384             6         10 jun 2021              17 jun 2022       1-Jan-2014
29333       532             2         8 oct 2023               10 oct 2025       1-Jan-2014

Any tips on the best way of achieving this?

CodePudding user response：

As I mentioned in the comments, LAG isn't well suited to this (unless you are on 2022). Instead, you'll likely want to use a derived table in the FROM:

SELECT YT.SPID,
       YT.ID,
       YT.Status,
       YT.StartDate,
       YT.EndDate,
       ISNULL(ED.EndDate,'19000101') AS PrevEdDate
FROM dbo.YourTable YT
     OUTER APPLY (SELECT TOP (1) oa.EndDate
                  FROM dbo.YourTable oa
                  WHERE oa.EndDate < YT.EndDate
                    AND oa.Status != 6
                  ORDER BY oa.EndDate DESC) ED;

If you were on 2022 (preview) then you could use LAG, and it's new utilise IGNORE NULLS feature alongside a CASE expression:

SELECT SPID,
       ID,
       Status,
       StartDate,
       EndDate,
       LAG(CASE WHEN Status != 6 THEN EndDate END,1,'19000101') IGNORE NULLS OVER (ORDER BY EndDate) AS PrevEd
FROM dbo.YourTable;

CodePudding user response：

You could do this:

lag(EndDate) over (order by iif(Status=6,1,0), EndDate)

It will sort by Status first, putting 6 at the bottom of the list, then use EndDate to find latest value with other status. Ofcourse, if there aren't any other previous statuses than 6, it'll still select it.