CODEWITHSUNDEEP
sqlsql-server
tinkertime
tinkertime

Reputation: 3042

SQL Latest Date

I have an interesting bit of logic that I can't seem to get my head around.

We have a Purchase table, where each Purchase is linked to a Customer and a Date. We want to pull out the Purchase that each customer made closest to the end of each month.

For instance,

CustomerID   |  Date
1            |  01/20/2009
2            |  01/26/2009
1            |  01/21/2009
1            |  02/02/2009

Should return....

CustomerID   |  Date
2            |  01/26/2009
1            |  01/21/2009
1            |  02/02/2009

Any ideas for a simple way to do this using SQL Server?

Upvotes: 2

Views: 1751

Answers (4)

Guffa
Guffa

Reputation: 700720

Group by the customer id and the year and date components of the date, and use the max aggregate to get the latest date from each group:

select CustomerId, max(Date)
from Purchase
group by CustomerId, datepart(year, Date), datepart(month, Date)

Note: If you are using SQL Server 2005 or later, you can use the year and month functions instead of datepart.

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147334

SELECT CustomerID, MAX(Date)
FROM Purchases
GROUP BY CustomerID, MONTH(Date), YEAR(Date)

Upvotes: 1

Kendrick
Kendrick

Reputation:

Group by month,year having max(day)

Upvotes: 0

David M
David M

Reputation: 72930

How about this?

SELECT  CustomerID, MAX(Date)
FROM    Purchase
GROUP BY CustomerID, YEAR(Date), MONTH(Date)

Upvotes: 12

Related Questions