Post reply

lead and lag records

  • February 24, 2022 at 7:01 pm

    #3988477

    Jeff Moden wrote:

    You original post talked about 1's and 0's.  You've now post data with only 1's and haven't posted the result that you're looking for.  I don't know about anyone else but I don't actually know what you're trying to do.

    I wonder if the requirement is to provide rows that represent a change of status, perhaps with first and last log dates for repeated status values. The rows before and after a change of status.

    Something like this:

    SELECT t.DivID,
           t.CustomerID,
           t.DivStatus,
           t.PreviousStatus,
           t.NextStatus,
           t.LogDate 
    FROM ( SELECT DivID, 
                  CustomerID,
                  DivStatus,
                  LogDate,
                  LAG(DivStatus,1,DivStatus) OVER (PARTITION BY DivID ORDER BY CustomerID, LogDate) AS PreviousStatus, 
                  LEAD(DivStatus,1,DivStatus) OVER (PARTITION  BY DivID ORDER BY CustomerID, LogDate) as NextStatus
           FROM dbo.Table1 
    )  AS t
    WHERE t.PreviousStatus <> t.DivStatus 
    OR    t.NextStatus <> t.DivStatus

    • This reply was modified 2 years, 9 months ago by  Ed B. Reason: Format code

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply