I have rows of data with a begin date and end date that are usually from the start of the month until the end of that month. However at times there are rows that are either - several months long - start in the middle of a month and end either the same month or a future month - begin at start of a month, but end somewhere int he middle of a future month.
i need to break these date ranges by month, but do not know how to go about this.
any guidance is appreciated.
thanks,
You could use a number table for this task.
In the sample query below a system table called master..spt_values is used as a replacement for the number table:
SELECT
CASE WHEN BeginDate > MonthStart THEN BeginDate ELSE MonthStart END AS BeginDate,
CASE WHEN EndDate < MonthEnd THEN EndDate ELSE MonthEnd END AS EndDate,
… /* other columns as needed */
FROM (
SELECT
d.*, /* or you could be more specific here */
(
DATEADD(month, DATEDIFF(month, 0, d.BeginDate) + v.number, 0)
) AS MonthStart,
DATEADD(day, -1,
DATEADD(month, DATEDIFF(month, 0, d.BeginDate) + v.number + 1, 0)
) AS MonthEnd
FROM RowsOfData d
INNER JOIN master..spt_values v ON v.type = 'P'
AND v.number BETWEEN 0 AND DATEDIFF(month, d.BeginDate, d.EndDate)
) s
Every row is split into a series of rows where BeginDate is either the actual BeginDate or the beginning of the month, depending on what value is greater, and likewise for EndDate. To illustrate, the following rows
BeginDate EndDate
---------- ----------
2010-03-05 2010-03-24
2010-04-16 2010-05-05
2010-06-29 2006-08-12
2010-10-10 2011-02-01
would split like this:
BeginDate EndDate
---------- ----------
2010-03-05 2010-03-24
2010-04-16 2010-04-30
2010-05-01 2010-05-05
2010-06-29 2010-06-30
2010-07-01 2010-07-31
2010-08-01 2010-08-12
2010-10-10 2010-10-31
2010-11-01 2010-11-30
2010-12-01 2010-12-31
2011-01-01 2011-01-31
2011-02-01 2011-02-02
One standard trick is to create a months table, and then select all months intersecting your date range.
You can take it one step farther and use a CASE statement in your select so that you'll take a start date for the month that is the maximum of the supplied start and the start of the month, and an end date for the month that is the minimum of the supplied end and the end of the month.
That will let you take a single date range and get a set of month date ranges. You can then join that query to your data set and group on the month to get your data divided up by month for a range of months. (This trick is particularly useful when some time periods may have no data, but you want them displayed. Just use a left join.)
