CODEWITHSUNDEEP
sql-server
sk7730
sk7730

Reputation: 736

SQL Old & New Values and Time Differences

I have Query which is providing below results.

    WORK_ITEM_ID    WORK_ITEM_STA_CD    CREAT_TS
    1                   8               2014-12-17 16:20:06.250
    1                   9               2014-12-17 16:23:48.430
    2                   8               2014-12-17 16:20:06.250
    2                   9               2014-12-17 16:24:36.930
    3                   8               2014-12-17 16:20:06.250
    4                   8               2014-12-17 16:20:06.250
    5                   8               2014-12-17 16:20:06.250

I want to have new column as OLD_STA_CD & Time Difference get result like below. can anyone please guide me to do that.

    WORK_ITEM_ID  OLD_STA_CD    WORK_ITEM_STA_CD    CREAT_TS               TimeDifference
    1               null            8           2014-12-17 16:20:06.250    
    1               8               9           2014-12-17 16:23:48.430    Thisrow - Previous Row
    2               null            8           2014-12-17 16:20:06.250
    2               8               9           2014-12-17 16:24:36.930    Thisrow - Previous Row
    3               null            8           2014-12-17 16:20:06.250
    4               null            8           2014-12-17 16:20:06.250
    5               null            8           2014-12-17 16:20:06.250

Upvotes: 0

Views: 39

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Use Window Function and Left join

;WITH cte
     AS (SELECT Row_number()OVER(partition BY WORK_ITEM_ID ORDER BY CREAT_TS ) rn,
                *
         FROM   tablename)
SELECT a.WORK_ITEM_ID,
       OLD_STA_CD= b.WORK_ITEM_STA_CD,
       a.WORK_ITEM_STA_CD,
       a.CREAT_TS,
       Time_Diff_In_Seconds=Datediff(ss, b.CREAT_TS, a.CREAT_TS)
FROM   cte a
       LEFT JOIN cte b
              ON a.WORK_ITEM_ID = b.WORK_ITEM_ID
                 AND a.rn = b.rn + 1

If you are using Sql Server 2012+ use LAG Function to fetch the previous row

SELECT WORK_ITEM_ID,
       OLD_STA_CD=Lag(WORK_ITEM_STA_CD)OVER(partition BY WORK_ITEM_ID ORDER BY CREAT_TS ),
       WORK_ITEM_STA_CD,
       CREAT_TS,
       Time_Diff_In_Seconds=Datediff(ss, Lag(CREAT_TS)OVER(partition BY WORK_ITEM_ID ORDER BY CREAT_TS ), CREAT_TS)
FROM   Tablename

Upvotes: 1

Related Questions