I would like to create a bar chart displaying the number of objects that were a available on a monthly base. All rows have a start and end date. I know how to do the count for a single month:
SELECT COUNT(*) As NumberOfItems
FROM Items
WHERE DATEPART(MONTH, Items.StartDate) <= @monthNumber
AND DATEPART(MONTH, Items.EndDate) >= @monthNumber
Now I would like do create the SQL to get the month number and the number of items using a single SELECT statement.
Is there any elegant way of accomplishing this? I am aware I have to take the year number into account.
Assuming Sql Server 2005 or newer.
CTE part will return month numbers spanning years between @startDate and @endDate. Main body joins month numbers with items performing the same conversion on Items.StartDate and Items.EndDate.
; with months (month) as (
select datediff (m, 0, @startDate)
union all
select month + 1
from months
where month < datediff (m, 0, @endDate)
)
select year (Items.StartDate) Year,
month (Items.StartDate) Month,
count (*) NumberOfItems
from months
inner join Items
on datediff (m, 0, Items.StartDate) <= months.month
and datediff (m, 0, Items.EndDate) >= months.month
group by
year (Items.StartDate),
month (Items.StartDate)
Note: if you intend to span more than hundred months you will need option (maxrecursion 0)
at the end of query.