SQL last day in moth select

Question

I have the following table which is basically the summary of daily transactions and is loaded nightly.

+++++++++++++++++++++++
+ DateCreated + Sale  +
+++++++++++++++++++++++
+ 20100101    + 1000  +
+ 20100131    + 2000  +
+ 20100210    + 2000  +
+ 20100331    + 4000  +
+++++++++++++++++++++++

I need to display the sale by month, but only for the last day in each month.

eg

JAN    2000
FEB       0
MAR    4000

I could probably accomplish this with CASE in my select, but is this efficient? This is SQL Server 2000.

Answer 1

My Q&D solution to this has been to construct a date that's the first day of the next month, then subtract a day.

Answer 2

I had a calendar table, just added a "IsLastDayOfMonth" column, set the last day of each month. Then joined that to my query, filtering by "IsLastDayOfMonth" only and that worked.

select a.datecreated,a.sale
         from salesummary a
 inner join calendar c
on a.datecreated = c.fulldatekey
 and c.IsLastDayOfMonth = 1
group by a.datecreated,a.sale

Answer 3

You could do something like this, but it will not show 0 for missing data:

select YEAR(DateCreated) as Year, MONTH(DateCreated) as Month, Sale
from MyTable
where DAY(DateCreated + 1) = 1