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

I have the attached SQL,that works fine for figuring out the amount of days booked in each month.
However as soon as the startDate and endDate are in different months it would give a false result, because its only grouped on the startDate.

How do i solve this issue ?
I want to know how many days were booked in each month.
SELECT   month(hotel_sDate),year(hotel_sDate),sum(DATEDIFF(d, hotel_sDate, hotel_eDate) )
FROM         hotelBooking
group by month(hotel_sDate),year(hotel_sDate)

Open in new window

mSchmidtAsked:
Who is Participating?
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
(sorry, hadn't refreshed to see you last post, glad you are testing :-)
I believe this one will work, across months, years whatever.  It is assuming that a visit thats starts on today and ends tomorrow is counted as 1 day for the checkin date.  Easily changed if want check out day.
WITH cteDays AS
(
SELECT Id,
	   hotel_sDate,
	   hotel_eDate,
	   hotel_sDate theDay
FROM hotelBooking HB
UNION ALL
SELECT D.Id,
	   D.hotel_sDate,
	   D.hotel_eDate,
	   D.theDay + 1
FROM cteDays D
WHERE D.theDay+1 < D.hotel_eDate 
) 
SELECT   year(theDay) Year, month(theDay) Month, COUNT(*) 
FROM         cteDays
group by year(theDay), month(theDay)

Open in new window

0
 
Patrick MatthewsCommented:
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)
0
 
RiteshShahCommented:
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' 
*/

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
SNilssonCommented:
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)

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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)

Open in new window

0
 
mSchmidtAuthor Commented:
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

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
 
Chris LuttrellSenior Database ArchitectCommented:
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?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Can we assume the time span is not crossing more than one year?
0
 
SNilssonCommented:
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

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The CTE is the definite solution, as long as you are on MSSQL 2005 or newer.
0
 
mSchmidtAuthor Commented:
thanks for everyone participating, i went with CTE solution, makes it very clean and doesnt need a table to join on.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.