StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWith SQL Server how to get the most recent date while summing on amount and the sum of the amount is greater than 0?
text
Body
copied!<p>I have a transaction table:</p> <pre><code>CREATE TABLE [dbo].[Trans]( [ID] [char](6) NOT NULL, [PersonID] [char](6) NULL, [TransCode] [char](2) NULL, [TransDesc] [char](45) NULL, [TransDate] [datetime] NULL, [TransAmount] [numeric](18, 2) NULL) </code></pre> <p>What I want is the date and amount of the most recent payment made by each person. Payments are defined by the TransCode (see the WHERE clause below).</p> <p>There can be multiple transactions on any given day, so I want the sum of transactions on the most recent day. Also there can be "reversal" transactions that could make the sum of the transactions on the most recent day be 0 (zero) -- I don't want these to be in the results (hence the "HAVING (SUM(TransAmount) > 0)" below).</p> <p>Here is my most recent attempt, that is not working, but I don't know why!</p> <pre><code>SELECT PersonID, MAX(TransDate) AS LastPaymentDate, TotalAmount FROM (SELECT PersonID, TransDate, SUM(TransAmount) AS TotalAmount FROM Trans WHERE (TransCode IN ('11', '12', '13', '14', '18', '19', '61', '63', '68', '70', '71', '72', '73', '74', '75', '76', '78', '79', '80', '81', '94', 'P2')) GROUP BY PersonID, TransDate HAVING (SUM(TransAmount) > 0) ORDER BY PersonID, TransDate) AS TotalAmount GROUP BY PersonID, TotalAmount ORDER BY PersonID </code></pre> <p>When I run the inner query I get what expect, a list of amounts summed by date, for each person. The outer query though, for some reason, is returning multiple rows for each person. It should only return one row for each person :-(</p> <p>Can someone one tell me what I am doing wrong?</p> <p>Please let me know if there is any additional information you may need to understand the problem better.</p> <p>Thank you in advance for any help you can provide,</p> <p>Ben</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload