Get the Recent changed value from History Table

  • I have a history table with the following values

    CREATE TABLE History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)

    INSERT INTO History VALUES ('03-23-2015','PT-01','2015-04-22')

    INSERT INTO History VALUES ('03-30-2015','PT-01','2015-04-20')

    INSERT INTO History VALUES ('04-06-2015','PT-01','2015-06-30')

    INSERT INTO History VALUES ('03-23-2015','PT-02','2015-04-22')

    INSERT INTO History VALUES ('03-30-2015','PT-02','2015-04-22')

    INSERT INTO History VALUES ('04-06-2015','PT-02','2015-04-22')

    INSERT INTO History VALUES ('03-23-2015','PT-03','2015-04-18')

    INSERT INTO History VALUES ('03-30-2015','PT-03','2015-04-22')

    INSERT INTO History VALUES ('04-06-2015','PT-03','2015-04-22')

    INSERT INTO History VALUES ('03-23-2015','PT-04','2015-04-20')

    INSERT INTO History VALUES ('03-30-2015','PT-04','2015-04-22')

    INSERT INTO History VALUES ('04-06-2015','PT-04','2015-04-18')

    I need an output in the below format. I need the most recent changed value for any given UID. Please help me in getting the below result

    OUTPUT

    UID PreviousDueDate CurrentDueDate

    ----------------------------------------

    PT-01 2015-04-20 2015-06-30

    PT-02 2015-04-22 2015-04-22

    PT-03 2015-04-18 2015-04-22

    PT-042015-04-222015-04-18

    Thanks in Advance

  • Use MAX(DATE) and GROUP BY (ID)

    😎

  • I want Current and Previous Due Date for every UID. Previous Due Date could be greater than Current Due Date. So I cannot use Max & Min here. I need a query where it uses SnapShotDate and checks for changes for every UID and gives previous and Current Due Dates.

    Hope that Explains!

  • v-swgar (4/6/2015)


    OUTPUT

    UID PreviousDueDate CurrentDueDate

    ----------------------------------------

    PT-01 2015-04-20 2015-06-30

    PT-02 2015-04-22 2015-04-22

    PT-03 2015-04-18 2015-04-22

    PT-042015-04-222015-04-18

    ;with cte as

    (

    Select UID, dueDate,

    ROW_NUMBER() over(partition by uid order by snapshotdate desc) rowNum

    from History

    )

    select c1.uid, c2.duedate PrevDueDate, c1.duedate CurrDueDate

    from cte c1

    inner join cte c2 on c2.uid = c1.uid and c2.rownum = 2

    where c1.rowNum = 1

    This gives your expected output for 3 out of the 4 rows. Why is the logic for PreviousDueDate for PT-03 different from the others? I could not see why that would be 4/18.

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Quick, simple and quite efficient solution

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID(N'dbo.History') IS NOT NULL DROP TABLE dbo.History;

    CREATE TABLE dbo.History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)

    INSERT INTO dbo.History VALUES ('03-23-2015','PT-01','2015-04-22')

    INSERT INTO dbo.History VALUES ('03-30-2015','PT-01','2015-04-20')

    INSERT INTO dbo.History VALUES ('04-06-2015','PT-01','2015-06-30')

    INSERT INTO dbo.History VALUES ('03-23-2015','PT-02','2015-04-22')

    INSERT INTO dbo.History VALUES ('03-30-2015','PT-02','2015-04-22')

    INSERT INTO dbo.History VALUES ('04-06-2015','PT-02','2015-04-22')

    INSERT INTO dbo.History VALUES ('03-23-2015','PT-03','2015-04-18')

    INSERT INTO dbo.History VALUES ('03-30-2015','PT-03','2015-04-22')

    INSERT INTO dbo.History VALUES ('04-06-2015','PT-03','2015-04-22')

    INSERT INTO dbo.History VALUES ('03-23-2015','PT-04','2015-04-20')

    INSERT INTO dbo.History VALUES ('03-30-2015','PT-04','2015-04-22')

    INSERT INTO dbo.History VALUES ('04-06-2015','PT-04','2015-04-18');

    ;WITH NUMBERED_DATES_BY_UID AS

    (

    SELECT

    H.UID

    ,ROW_NUMBER() OVER

    (

    PARTITION BY H.UID

    ORDER BY H.DUEDATE DESC

    ) AS UID_RID

    ,H.DUEDATE

    FROM dbo.History H

    )

    SELECT

    ND.UID

    ,MAX(CASE WHEN ND.UID_RID = 2 THEN ND.DUEDATE END) AS PREVIOUS_DUEDATE

    ,MAX(CASE WHEN ND.UID_RID = 1 THEN ND.DUEDATE END) AS CURRENT_DUDATE

    FROM NUMBERED_DATES_BY_UID ND

    GROUP BY ND.UID;

    Results

    UID PREVIOUS_DUEDATE CURRENT_DUDATE

    ---------- ----------------------- -----------------------

    PT-01 2015-04-22 00:00:00.000 2015-06-30 00:00:00.000

    PT-02 2015-04-22 00:00:00.000 2015-04-22 00:00:00.000

    PT-03 2015-04-22 00:00:00.000 2015-04-22 00:00:00.000

    PT-04 2015-04-20 00:00:00.000 2015-04-22 00:00:00.000

    Bonus message

    Warning: Null value is eliminated by an aggregate or other SET operation.

    The purpose of the aggregation here is only to eliminate the NULL values;-)

Viewing 5 posts - 1 through 4 (of 4 total)

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