Please see Counting days per month in a date range, part 1 of 2 for the complete problem statement.
Now we have to count the number of days in each month. As illustrated by our timeline diagram, we must be sensitive to whether the month actually contains Begin_Date or End_Date. For any given month, the lower end of the range of dates that need to be counted is either the first of that month or Begin_Date, whichever is greater. Also, for any given month, the upper end of the range of dates that need to be counted is either the first of the next month, or the day after End_Date, whichever is less. We need the first of the next month (rather than the last day of the month) or the day after End_Date (rather than End_Date) because we want to include the last day in the count.
We can handle these end-point conditions either with CASE expressions, or, since we're using MySQL syntax, by taking advantage of the convenient LEAST and GREATEST functions.
Here's our final query:
select Begin_Date , End_Date , date_format( date_add( date_add(Begin_Date , interval -dayofmonth(Begin_Date)+1 day ) , interval i month ) , "%Y/%m" ) as YR_MTH , to_days(least( date_add( date_add(Begin_Date , interval -dayofmonth(Begin_Date)+1 day ) , interval i+1 month ), date_add(End_Date, interval 1 day) )) - to_days(greatest( date_add( date_add(Begin_Date , interval -dayofmonth(Begin_Date)+1 day ) , interval i month ), Begin_Date )) as DAYS from integers inner join datatable on date_add( date_add(Begin_Date , interval -dayofmonth(Begin_Date)+1 day ) , interval i month) between date_add(Begin_Date , interval -dayofmonth(Begin_Date)+1 day ) and End_Date group by Begin_Date , End_Date , YR_MTH order by Begin_Date , YR_MTH
Note the use of the MySQL TO_DAYS function to assist in the calculation of elapsed days. TO_DAYS takes a date and returns a day number which represents the number of days since some base date in the past (which date this is doesn't matter). Subtracting a smaller day number from a larger day number therefore gives the number of elapsed days between those two dates. In other databases (and in MySQL after version 4.1.1), a DATEDIFF function may be used instead.
If you wish to try out this query yourself, you may use the following test script:
create table datatable ( id tinyint not null primary key auto_increment , Begin_Date date , End_Date date ); insert into datatable (Begin_Date, End_Date) values ( '2004-01-23' , '2004-09-09' ) ,( '2004-02-02' , '2004-02-05' ) ,( '2004-03-01' , '2004-04-30' ) ,( '2004-05-01' , '2004-06-01' ) ,( '2004-10-01' , '2005-01-06' ) ;
When developing a query, it's important to have comprehensive test data with conditions that cover all possibilities, e.g. date range is only one day, date range is within one month, date range crosses year-end boundary, etc.
Dig Deeper on SQL Server Database Modeling and Design
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.