removing dups by min dates keeping the max but sum all the values 1

[ciarra41]

Hi there,
I've recently submitted a thread #183-1648000 to remove all the min dated records but it kept the max.

I need to do the same thing but I need it to add the values in each of the rows before it roll up to max date. The sql below works but it only summing the max value from the date. I would like for it to sum all the rows per id but keep the record with the max date.



Select id, sum(value1), sum(value2), sum(value3)From
YourTableNameHere T
Left Join ( Select ID, Max(Dates) As MaxDate
From YourTableNameHere Group BY ID ) As A
On T.ID = A.ID And T.Dates = A.MaxDate
group by id

 

[markros]

SQL Server 2005 and up:

;with cte as (select *, 
ROW_NUMBER() over (partition by ID order by [Date]) as Row,
sum(Value1) over (partition by ID) as [Total Value1], 
sum(Value2) over (partition by ID) as [Total Value2], 
sum(Value3) over (partition by ID) as [Total Value3] 
FROM TableName)

select * from cte where Row = 1

PluralSight Learning Library

 

[ciarra41]

Thank you this worked great.