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

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.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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.

This was first published in November 2004

Back to top