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

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

I have two columns in a SQL table that contain dates in a date range. I need to count the days in the date range...

and group by year/month. If the dates in the range are in the same month or in two consecutive months I can calculate the days. If the dates span two or more months, I don't know how to group by the months in between the begin date and end date.

For example, if Begin_Date is 2004-10-01 and End_Date is 2005-01-06, how do I group by each month in the date range so the output looks something like this:

 YR_MTH DAYS 2004/10 31 2004/11 30 2004/12 31 2005/01 6

Any assistance would be appreciated.

Let's go through this slowly. The SQL will be illustrated with MySQL syntax. If you're using some other database system, there are comparable date functions for everything we'll need to do.

To start, take the day of the month of Begin_Date, subtract that many days from Begin_Date, and then add 1.

 date_add(Begin_Date , interval -dayofmonth(Begin_Date)+1 day)

For example, if Begin_Date is November 2nd, then going back 2 days gives the last day of October, and then going forward 1 day gives November 1st. If Begin_Date is November 19th, then going back 19 days gives the last day of October, and then going forward 1 day gives November 1st again. This formula works no matter what day of the month Begin_Date is, and it gives the first day of the month that contains Begin_Date.

Now that we have the first day of the first month, we will generate all the months up to End_Date. Once again, we will use the integers table.

 select date_add( date_add(Begin_Date , interval -dayofmonth(Begin_Date)+1 day) , interval i month) from integers inner join yourtable on date_add( date_add(Begin_Date , interval -dayofmonth(Begin_Date)+1 day) , interval i month) between Begin_Date and End_Date

Here i is an integer which will run from 0 upwards to some number. The exact number is determined by adding i months to the date of the first day of the month that contains Begin_Date. Note that i begins with 0, so we start with the month that contains Begin_Date. Any i value added to this starting date which results in a date that is greater than End_Date will not satisfy the BETWEEN condition in the ON clause. Thus we generate the dates of the first day of all months in the range covering Begin_Date to End_Date.

Here's a convenient timeline diagram to illustrate the date range and its end points:

 date of date of date of date of first day first day first day first day of month of month of month of month containing containing containing containing Begin_Date Begin_Date Begin_Date Begin_Date + 0 months + 1 month + n months + n+1 months | | | | | | | | | Begin_ | | End_ | | Date | | Date | | | | | | | | | | | | | | | | | | | ---|---B---------|----- ... ----|-----E-------|--- <=============== ... ==========>

Please proceed to Counting days per month in a date range, part 2 of 2.

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.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close