locked
Difference of last 2 Rows with Start and End Time RRS feed

  • Question

  • Hi all,

      This question is an extention to the below question

    http://social.msdn.microsoft.com/Forums/en-US/ca938faf-49a7-4212-afd2-19046cbfcc8b/difference-of-last-2-rows?forum=transactsql

    I got the difference of last 2 rows from the above question. But now the table added with one field StartTime. The final table is

    CREATE TABLE MeterReading
    (
     ReadingID int,
     MeterID int,
     DataCounter int,
     StartTime datetime,
     Value numeric(16,4)
    )
    

    As per the my first thread i got the best answer like below

    SELECT MeterID, SUM(CASE WHEN Recency = 1 THEN Value ELSE -Value END)
     FROM (
     select DISTINCT MeterID,  Value, DENSE_RANK() OVER (PARTITION BY MeterID  ORDER BY StartTime DESC) 'Recency'
     from MeterReading
     ) x
     WHERE Recency IN (1,2)
     GROUP BY MeterID
    Now i need to add StartTime to the result. The expectation is, it will take the difference of last and second last record. So i need the starttime as secondlast record starttime and endtime as last record startime. Please help me

    Friday, August 22, 2014 4:29 AM

Answers

  • You can simply extend your first query as this

    SELECT 
    ProcessParameterID
    
    ,MAX(CASE WHEN Recency = 2 THEN StartTime ELSE NULL  END) StartTime
    ,MAX(CASE WHEN Recency = 1 THEN StartTime ELSE NULL END) EndTime
    , SUM(CASE WHEN Recency = 1 THEN Value ELSE -Value END) Value
     FROM (
     select ProcessParameterID, StartTime, Value, DENSE_RANK() OVER (PARTITION BY ProcessParameterID  ORDER BY StartTime DESC) 'Recency'
     from LT_LAEMI_HWT_Readings
     ) x
     WHERE Recency IN (1,2)
     GROUP BY ProcessParameterID
     


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by akhilrajau Friday, August 22, 2014 6:05 AM
    Friday, August 22, 2014 5:15 AM

All replies

  • I got a solution like below

    SELECT
    y.ProcessParameterID
    ,MIN(y.StartTime) StartTime
    ,MAX(y.EndTime) EndTime
    ,SUM(y.Value) Value
    FROM
    (
    SELECT 
    ProcessParameterID
    
    ,(CASE WHEN Recency = 2 THEN StartTime ELSE null END) StartTime
    ,(CASE WHEN Recency = 1 THEN StartTime ELSE null END) EndTime
    , SUM(CASE WHEN Recency = 1 THEN Value ELSE -Value END) Value
     FROM (
     select DISTINCT ProcessParameterID, StartTime, Value, DENSE_RANK() OVER (PARTITION BY ProcessParameterID  ORDER BY StartTime DESC) 'Recency'
     from LT_LAEMI_HWT_Readings
     ) x
     WHERE Recency IN (1,2)
     GROUP BY ProcessParameterID
     , Recency
     , StartTime
     ) y  
     GROUP BY ProcessParameterID
    Can you advice me, is this the correct way or any alternative short method

    Friday, August 22, 2014 5:04 AM
  • You can simply extend your first query as this

    SELECT 
    ProcessParameterID
    
    ,MAX(CASE WHEN Recency = 2 THEN StartTime ELSE NULL  END) StartTime
    ,MAX(CASE WHEN Recency = 1 THEN StartTime ELSE NULL END) EndTime
    , SUM(CASE WHEN Recency = 1 THEN Value ELSE -Value END) Value
     FROM (
     select ProcessParameterID, StartTime, Value, DENSE_RANK() OVER (PARTITION BY ProcessParameterID  ORDER BY StartTime DESC) 'Recency'
     from LT_LAEMI_HWT_Readings
     ) x
     WHERE Recency IN (1,2)
     GROUP BY ProcessParameterID
     


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by akhilrajau Friday, August 22, 2014 6:05 AM
    Friday, August 22, 2014 5:15 AM