• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

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

0
mSchmidt
Asked:
mSchmidt
  • 4
  • 2
  • 2
  • +3
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
Can we assume the time span is not crossing more than one year?
0
 
Chris LuttrellSenior 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
 
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
 
QlemoC++ DeveloperCommented:
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now