Sql-server – Return Most Recent Entry But Sum One Field

sql serversql-server-2008-r2t-sql

What I am after here is to return the Max(date) but Sum(amt) so that way I only see the most recent information pertaining to an account, but I also see the Total Amt! This is my DDL

Declare @Helper1 Table
(name varchar(100),inputid int, custid varchar(100), rdsdate date, amt decimal(10,2))

Insert Into @Helper1 (name, inputid, custid, rdsdate, amt) Values
('Bob', 123, 'rst11', '2017-01-01', '13.00'), ('Jack', 131, 'rst11', '2017-01-05', '-5.00')
,('Larry', 444, 'ems33', '2017-02-01', '10.00')

I tried this query – however it returns all info for custid rst11 not the Max() like I am after

Select
name
,inputid = MAX(inputid)
,custid
,rdsdate = MAX(rdsdate)
,amt = SUM(amt)
FROM @Helper1
GROUP BY name, custid
ORDER BY name asc

This is my desired returned result set (since rst11 has two entries return the max and SUM())

Jack 131 rst11 2017-01-05 8.00
Larry 444 ems33 2017-02-01 10.00

What is the proper way to achieve this in a query?

Best Answer

You can use a combination of ROW_NUMBER and SUM() OVER():

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER( PARTITION BY custid
                                    ORDER BY rdsdate),
            total_amt = SUM(amt) OVER(PARTITION BY custid)
    FROM @Helper1
)
SELECT  name,
        inputid,
        custid,
        rdsdate,
        total_amt
FROM CTE
WHERE RN = 1;
Related Question