select only the last day of any month

[OldSlowDog]

Hello,
I have a table storing daily sales of our four stores. I need to create a sales report showing only sales on the last day of every month for each store going back as far as we can.

I tried MAX(day(SalesDate)) and it is not working.
Select Max(day(SalesDate), StoreLoc, SalesAmt
FROM Sales
Group by StoreLoc, SalesDate
Order by StoreLoc, SalesDate

Please help.

 

[ousoonerjoe]

Take a look at this thread from a while back... i had a similar issue:

thread222-1440908

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[jbenson001]

What does that thread have to do with SQL?
Try:

SELECT DATEADD(dd, -DAY(DATEADD(m,1,getdate())), DATEADD(m,1,@date))

 

[djj55]

Take a look at FAQ faq183-6628

 

[ousoonerjoe]

Grabbed the wrong posting looks like. sorry about that.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.