asked
How to subtract 2 rows based on min and max dates?
Table : Create table Events ( ID int, Events int, eventsdate SmallDateTime); Data : ID Events eventsdate 3 2 23-12-2015 00:00 3 7 24-12-2015 00:00 3 4 25-12-2015 00:00 6 10 23-09-2015 00:00 6 7 24-09-2015 00:00 6 4 25-09-2015 00:00 9 4 25-09-2015 00:00 OutPut : ID Events 3 -5 6 3 Note : while display the out put we have to consider below points Have to select top most max date record events and have to subtract this event value with second max events date records. max records for ID =3; 3 2 23-12-2015 00:00 3 7 24-12-2015 00:00 Max records for Id=9 6 10 23-09-2015 00:00 6 7 24-09-2015 00:00 Please help on this solution
This solution orders the rows per id by the date in ascending order and finally selects the events for the oldest date and the second oldest date and subtracts the first with the second. WITH cte AS ( SELECT
e.id, e.events, ROW_NUMBER() OVER(PARTITION BY
e.id ORDER BY e.eventsdate) as rownum FROM #events e ) SELECT
m1.id, m1.events - m2.events as Events FROM cte m1 INNER JOIN cte m2 ON
m1.id =
m2.id AND m1.rownum=1 AND m2.rownum=2
17 People are following this question.
