Solved

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

Posted on 2009-04-19
469 Views
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)
``````
0
Question by:mSchmidt

LVL 92

Expert Comment

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

LVL 31

Expert Comment

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'

*/
``````
0

LVL 8

Expert Comment

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

datepart(day, hotel_sDate), hotel_sDate)))

END)

) )

FROM

hotelBooking

group by month(hotel_sDate),year(hotel_sDate)
``````
0

LVL 67

Expert Comment

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)
``````
0

Author Comment

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

LVL 67

Expert Comment

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

LVL 26

Expert Comment

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

LVL 67

Expert Comment

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

LVL 26

Accepted Solution

(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)
``````
0

LVL 8

Expert Comment

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
``````
0

LVL 67

Expert Comment

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

Author Comment

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

## Featured Post

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, usâ€¦
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even withinâ€¦
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.