Sum of days, grouped by month and year (date range across months problem)

I would set up a second table, notionally tblCalendar, that simply has every day listed in it.  Your query
then becomes something like this:

SELECT MONTH(c.TheDate), YEAR(c.TheDate), COUNT(*) AS TotDays
FROM hotelBooking h INNER JOIN
      tblCalendar c ON c.TheDate >= h.hotel_sDate AND c.TheDate < h.hotel_eDate
GROUP BY MONTH(c.TheDate), YEAR(c.TheDate)

see, its not giving false results. I checked it with given data, have a look. results it is giving is,

----------- ----------- -----------
1           2009        53
2           2009        30
3           2009        2

(3 row(s) affected)


which is perfectly right

SELECT   month(sDate),year(sDate),sum(DATEDIFF(d, sDate, eDate)) 
FROM         FindDiff
group by 
month(sDate),year(sDate)
 
 
 
/*
create table findDiff
(
sdate datetime,
edate datetime
)
 
insert into findDiff
SELECT '02-26-2009' , '03-04-09' UNION ALL
SELECT '02-02-2009' , '02-04-09' UNION ALL
SELECT '02-02-2009' , '02-24-09' UNION ALL
SELECT '01-02-2009' , '02-24-09' UNION ALL
SELECT '03-01-2009' , '03-03-09' 
*/

Select allOpen in new window

Try this out:

SELECT   month(hotel_sDate),year(hotel_sDate),sum(DATEDIFF(d, hotel_sDate,( 
CASE WHEN month(hotel_eDate) = Month(hotel_sDate) THEN 
hotel_eDate
ELSE
dateadd(
day, -1, dateadd(month, 1, 
dateadd(day, 1- 
datepart(day, hotel_sDate), hotel_sDate)))
END)
) )
FROM 
hotelBooking
group by month(hotel_sDate),year(hotel_sDate)

Select allOpen in new window

Assuming there is not more than one month difference:

SELECT   month(hotel_sDate),year(hotel_sDate),sum(DATEDIFF(d, hotel_sDate, hotel_eDate) )
FROM
( select hotel_sdate,
    case when month(hotel_edate) > month(hotel_sdate)
	 then convert(datetime,left(convert(varchar, hotel_edate, 112),6) + '01')
	 else hotel_edate
    end as hotel_edate
  from hotelbooking
  union all
  select dateadd(m, 1, left(convert(varchar, hotel_sdate, 112),6) + '01'),
     hotel_edate
  from hotelbooking
  where month(hotel_edate) <> month(hotel_sdate)
) hotelbooking 
group by month(hotel_sDate),year(hotel_sDate)

Select allOpen in new window

matthewspatrick:
This would actually give the correct result, but it seems tidious to have an extra table.

RiteshShah:
It gives a wrong result, your example should have only 30 days in JAnuary. not 53

SNilsson:
Your example code would only count days which are in the same month as the startdate

Qlemo:
there may very well be more than one month and should also give correct results when going from
28-12-2008 - 03-01-2009

If you have more than one month difference, you have to use a cursor, or the calendar method suggested by matthewspatrick.

Reason: You have to make n records out of one, as I showed in the ad-hoc view.

I could extend my code to 2 or 3 or 4, but not arbitrary months.

Instead of having a complete calendar, it would be sufficient to have one entry per month, let's say each first. That way you have 12 entries per year, and you can easily generate records for each month's interval.

I hope you are testing all these, I am working on a "working" solution and have tested all these queries against a known test set and they do not work.

Mathew is the only one on the right track with a tblCalendar idea.  I am working on a TSQL script that would solve this without that physical table.

By the way, if the sDate is Dec 31 and the eDate is Jan 1, does that count as 2 days total, 1 in Dec and 1 in Jan?

Can we assume the time span is not crossing more than one year?

If there is a no more than one month difference maybe you could do like this based on my previous (half)working example:

SELECT month, year, count(NrOfdays)
FROM(
Select month, year, NrOfdays(year/month = year/month returning days to end of month)
UNION ALL
Select month, year, NrOfdays(year/month > year/month returning days from begining of month)
) subQ
Group by month,year

Select allOpen in new window

The CTE is the definite solution, as long as you are on MSSQL 2005 or newer.

thanks for everyone participating, i went with CTE solution, makes it very clean and doesnt need a table to join on.