Monthly count of objects with start and end date using TSQL

Go To StackoverFlow.com

1

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.

2012-04-04 08:03
by jhoefnagels
So to clarify "I am aware I have to take the year number into account", do you want 12 rows no matter what the date range of items, or do you want (eg) March 2011 and March 2012 as different rows - AakashM 2012-04-04 08:36


3

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.

2012-04-04 08:22
by Nikola Markovinović
Ads