SQL Latest Date

Question

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?

Answer 1

How about this?

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

Answer 2

Group by month,year having max(day)

Answer 3

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

Answer 4

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.