0

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?

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.