Post reply

Creating a history table from a single table

  • June 14, 2017 at 12:09 pm

    #324095

    I needed to created a view showing the "from" and "to" statuses from a single table. Here is some example data to create the table:

    CREATE TABLE History (
        pID INT,
        Stat VARCHAR(25),
        Dt DATE
    )

    INSERT INTO History (pID, Stat, Dt)
    VALUES
        (1111, 'Secondary', '2017-06-01'),
        (2222, 'Secondary', '2017-06-02'),
        (1111, 'Fee Paid', '2017-06-03'),
        (1111, 'App Complete', '2017-06-04'),
        (2222, 'Fee Paid', '2017-06-04'),
        (2222, 'App Complete', '2017-06-05')

    This is my attempt to create a result set showing what Stat a person went from and to on the same row:

    WITH x1 AS (
        SELECT ROW_NUMBER() OVER(PARTITION BY pID ORDER BY Dt DESC) AS rn, *
        FROM History
    )

    SELECT x1.pID, x2.Stat AS PrevStat, x1.Stat AS NewStat, x1.Dt AS NewStatDt
    FROM x1
        JOIN x1 x2 ON x1.pID = x2.pID
            AND x1.rn = x2.rn-1

    However, if the Stat is the first one in the date sequence I need the PrevStat to be NULL and the NewStat to be the first one in the date sequence.

    So here is what I am getting:

    But here is what I am hoping to get:

    Can anyone help me out? Thanks!

  • June 14, 2017 at 12:57 pm

    #1946831

    change your join X1 X2 to be a LEFT OUTER JOIN

    or you could use:

    SELECT x1.pID, LAG (Stat, 1, NULL) OVER (PARTITION BY x1.pID ORDER BY x1.Dt) AS PrevStat, x1.Stat AS NewStat, x1.Dt AS NewStatDt
      FROM #History x1

  • June 14, 2017 at 2:23 pm

    #1946856

    Haha yes, I don't know how I missed doing that. Thanks a bunch for the help!

Viewing 3 posts - 1 through 2 (of 2 total)

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