Select the number of days a record is present within a certain year/month

Question

In sql server I have a table with start and end dates for job postings. Given a month and a year by the user I need to find the amount of days between the start and end date that fall in the given year/month for that posting.

So if the posting Start Date is 2010/11/15 and End Date is 2010/12/05 Then output should be:

November  16 days
December  5 days
Total     21 days

I've been beating my head against the wall with this one and am fresh out of ideas.

Answer 1

This is probably the most concise answer.

declare @start datetime, @end datetime
select @start = '20101115', @end = '20101205'

select datename(month,@start+number), count(*)
from master..spt_values
where type='P'
  and number between 0 and datediff(d,@start,@end)
group by datename(month,@start+number), convert(char(6),@start+number,112)
order by convert(char(6),@start+number,112)

It will work for ranges up to 2048 days (7-8 years) but can by extended for longer if you need (on request only - that will looks more complicated).

The only reason for the convert(char portion is to make November come up before December, and also before January of the next year.