SELECT
	ClientId,
	SUM(y1.TransactionAmount)/100.0 AS TotalDollars
FROM
	YourTable y1
	CROSS APPLY
	(
		SELECT MAX(TransactionDate) AS Dt FROM YourTable y2 WHERE y1.ClientId = y2.clientId
	) y2
WHERE
	y1.TransactionDate = y2.Dt
GROUP BY
	ClientId;
	
SELECT
	ClientId,
	SUM(y1.TransactionAmount)/100.0 AS TotalDollars
FROM
	YourTable y1
WHERE
	y1.TransactionDate = 
	(
		SELECT MAX(TransactionDate) FROM YourTable y2 
		WHERE y2.ClientId = y1.clientId
	)
GROUP BY
	ClientId;

quote:

---

Originally posted by schin

We're running SQL Server 2008R2. We have a database with some tables that have information about clients, which includes fields like ClientID, TransactionDate, TransactionAmount, etc. We have a number of clients who are identified by a number, and each client will usually have multiple transactions on multiple dates. What I am trying to do is put together a script that gives me the total of TransactionAmount only for the most recent TransactionDate. ClientID and TransactionAmount are of type INT (the amount field is stored in cents, so we have to divide by 100 to display the dollar amount). TransactionDate is obviously a Date/Time type, storing only the date with no time information. The data looks something like (sorry for the odd look to the data, I am new to the forum and don't know how to format the text properly yet):

ClientID TransactionAmount TransactionDate
23 12032 2011-10-03 00:00:00.0000
23 218327 2011-10-03 00:00:00.0000
23 0 2011-10-03 00:00:00.0000
23 116052 2011-04-30 00:00:00.0000
88 482591 2011-06-12 00:00:00.0000
88 0 2011-06-12 00:00:00.0000
88 12652 2011-06-12 00:00:00.0000
88 332684 2011-01-16 00:00:00.0000

If this were all of the data, we'd like to total the amount for Client 23 only the transactions on October 3, 2011 and the amount for Client 88 only the transactions on June 12, 2011. For some reason, using the SUM() function as part of the SELECT statement gives me a completely wrong total. Any ideas?



Steve Chin

---

SELECT ClientID,TransactionDate,SUM(TransactionAmount) AS TotalAmt
FROM 
(SELECT DENSE_RANK() OVER (PARTITION BY ClientID ORDER BY TransactionDate DESC) AS Rn,*
FROM table
)t
WHERE Rn=1
GROUP BY ClientID,TransactionDate