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.