Hi Team, i need some help to get below output

Transact-SQL
1

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

Note : I have to take max eventsdate Event value and have to subtract that event value with second max eventdate event value .

Report_Output

ID Events
3 -3
6 3

2 
; with cte as
(
    select    *, rn = row_number() over (partition by ID order by eventsdate desc)
    from    Events
)
select    c.ID, Events = sum(case when rn = 1 then Events else -Events end)
from    cte c
where    c.rn    <= 2
group by c.ID
having count(*) = 2
3

Thanks, Khtan. Its working fine fore me :slight_smile: :slight_smile:

4

you are welcome. Glad to hear that :grinning:

5

select a.id,sum(case when Row_Num=1 then events else events*-1 end)events from(
select ,row_number() over (partition by id order by eventsdate desc )Row_Num from Events)a
WHERE Row_Num<=2
group by id having count()=2