Problem solve Get help with specific problems with your technologies, process and projects.

Counting days per month in a date range, part 2 of 2

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(
          , interval -dayofmonth(Begin_Date)+1 day )        
          , interval i month  ) 
          , "%Y/%m" )            as YR_MTH 
     , to_days(least(
          , interval -dayofmonth(Begin_Date)+1 day )        
          , interval i+1 month  ),            
          date_add(End_Date, interval 1 day) )) 
     - to_days(greatest(
          , interval -dayofmonth(Begin_Date)+1 day )        
          , interval i month  ), 
          Begin_Date ))          as DAYS
  from integers
  join datatable
    on date_add(
          , interval -dayofmonth(Begin_Date)+1 day )        
          , interval i month) 
          , interval -dayofmonth(Begin_Date)+1 day )  
   and End_Date
    by Begin_Date
     , End_Date
     , YR_MTH    
    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.

This was last published in November 2004

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.