Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Last rep billing a client

Author  Topic 

jaimealvarez
Starting Member

31 Posts

Posted - 2012-12-27 : 09:41:22
Hi,
I need a report that shows the last day that a client was billed and also the rep name that billed the client.

I know how to do the last billed date using MAX, but not sure how to bring the name too.

My fields are:

Client, date, Rep
Table T

So basically I need only one line per client, showing the last date and the Rep name.

ANy help would be appreciated.
Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-27 : 09:45:38
If you have just one client for whom you want to get the data
SELECT TOP (1) Client, Date, Rep 
FROM Tbl t
WHERE Client = 'JaneDoe'
ORDER BY Date DESC
For all clients

SELECT Client, Date, Rep
FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Date DESC) AS RN
FROM Tbl
)s
WHERE RN = 1;
Go to Top of Page
   

- Advertisement -