none
How to get the check the most recent data

    Question

  • Hello All,

    I have an audit table where we typically store the changes. The audit table is like this

    PODS ID                 Old Value                    New Value               ChangeDate

    23                           Non Regulated            Regulated                2013-06-06

    23                           Regulated                   Non Regulated          2013-06-08

    23                           Non Regulated            Regulated                 2013-07-14

    54                           NON Regulated            Regulated                2013-07-23

    54                           Regulated                   Non Regulated          2013-08-15

    I am looking for all the pods ids that got changed from Non Regulated to Regulated on its most recent change date. PODS ID 23 changed from Non Regulated to regulated on 2013-06-06 but again it got changed from regulated to non regulated on 2013-06-08 but the latest date says it again changed from Non regulated to regulated on 2013-07-14. So I need that pods id in the result set. But in the case of PODS id 54 even it got changed from non regulated to regulated on 2013-07-23 but later again it changed back to regulated on 2013-08-15 so I dont want this to be in the result set. Bottomline is first I need to check the most recent date of each pods id and check for that most recent check date whether the old value is Non regulated and new value is regulated and bring only that pods id details in the result set.

    Please help me with the logic. Appreciate your help. Let me know if you are not clear.

    Thanks

    Thursday, February 13, 2014 9:20 PM

Answers

  • Hi srisql, please use below..

    DECLARE @Table TABLE
    (
    PodsID INT,
    OldValue VARCHAR(20),
    NewValue VARCHAR(20),
    ChangeDate DATE
    )

    INSERT INTO @Table VALUES (23,'Non Regulated','Regulated','2013-06-06')
    , (23,'Regulated','Non Regulated','2013-06-08')
    , (23,'Non Regulated','Regulated','2013-07-14')
    , (54,'NON Regulated','Regulated','2013-07-23')
    ,(54,'Regulated', 'Non Regulated','2013-08-15')

    ;WITH CTE AS
    (
    SELECT * , ROW_NUMBER() OVER (PARTITION BY PodsID ORDER BY ChangeDate DESC) AS Rnum
    FROM @Table
    )
    SELECT PodsID, OldValue, NewValue, ChangeDate FROM CTE
    WHERE Rnum = 1 AND OldValue = 'Non Regulated' AND NewValue = 'Regulated'
    Read more about Ranking functions in an article I have written here at http://sqlsaga.com/sql-server/what-is-the-difference-between-rank-dense_rank-row_number-and-ntile-in-sql-server/

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.



    Thursday, February 13, 2014 9:45 PM
  • ;With mycte as
    (SELECT [PODS ID],[Old Value],[New Value], [ChangeDate], Row_number() Over(Partition By [PODS ID] Order By [ChangeDate]  DESC ) rn  FROM yourtable
    WHERE [New Value] ='Regulated')
    
    Select [PODS ID],[Old Value],[New Value], [ChangeDate] 
    FROM mycte 
    WHERE rn=1


    Thursday, February 13, 2014 9:47 PM
    Moderator

All replies

  • Hi srisql, please use below..

    DECLARE @Table TABLE
    (
    PodsID INT,
    OldValue VARCHAR(20),
    NewValue VARCHAR(20),
    ChangeDate DATE
    )

    INSERT INTO @Table VALUES (23,'Non Regulated','Regulated','2013-06-06')
    , (23,'Regulated','Non Regulated','2013-06-08')
    , (23,'Non Regulated','Regulated','2013-07-14')
    , (54,'NON Regulated','Regulated','2013-07-23')
    ,(54,'Regulated', 'Non Regulated','2013-08-15')

    ;WITH CTE AS
    (
    SELECT * , ROW_NUMBER() OVER (PARTITION BY PodsID ORDER BY ChangeDate DESC) AS Rnum
    FROM @Table
    )
    SELECT PodsID, OldValue, NewValue, ChangeDate FROM CTE
    WHERE Rnum = 1 AND OldValue = 'Non Regulated' AND NewValue = 'Regulated'
    Read more about Ranking functions in an article I have written here at http://sqlsaga.com/sql-server/what-is-the-difference-between-rank-dense_rank-row_number-and-ntile-in-sql-server/

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.



    Thursday, February 13, 2014 9:45 PM
  • ;With mycte as
    (SELECT [PODS ID],[Old Value],[New Value], [ChangeDate], Row_number() Over(Partition By [PODS ID] Order By [ChangeDate]  DESC ) rn  FROM yourtable
    WHERE [New Value] ='Regulated')
    
    Select [PODS ID],[Old Value],[New Value], [ChangeDate] 
    FROM mycte 
    WHERE rn=1


    Thursday, February 13, 2014 9:47 PM
    Moderator