You are here:

## Blog Archive:

* Can be used in order to search for older blogs Entries

## Search in blogs

 KeywordsPhrase

## Blog Categories:

* Can be used in order to search for blogs Entries by Categories

## Blog Tags:

* Can be used in order to search for blogs by keywords

## TNWikiSummit

 Read this before you use the blog!

## Split Date Range into Months

מרץ31

Written by: ronen ariely
31/03/2014 02:21

## introduction

In several cases in life we encounter a date range defined by its start and end dates, and we need to split the range into months included in that range. For example, we may need to find the months include with in a given date range, or in a bit more complex demand, we may need to find the exact date range in each of the months include with in a given date range. In this case if our date range is defined by starting date 2014-03-14, and ending date of 2014-05-16 then our result set should be:

From Date       To Date
2014-03-14     2014-03-31
2014-04-01     2014-04-30
2014-05-01     2014-05-16

This short article we will show how we can split a single date's range (start date, end date) into the data set of months which included in that range. In the next step we will use this solution to break a set of date ranges (table) into one set of months.

## Our Case Study

We got a table with date ranges records, defined by its start and end dates. Each record include a value for the date range, for example it can be total number of hours we work on a project. Our goal is to split the working hours into months, relative to the number of days in that month included in the date range.

for example if we work totally of 20 hours from 2014-02-26 to 2014-03-02 then our original (source) data is:

Start Time        End Time        Working Hours
2014-02-26       2014-03-02     20

We worked 3 days in February and 2 days in March. Since we have total number of 20 then we will split the 20 hours into 5 days, so each day have average time of 4 working hours, therefor our result set will be:

Start Time        End Time        Days      Average Working Time This month
2014-02-26      2014-02-28    3             4*3 = 12
2014-03-01      2014-03-02    2             4*2 = 8

Let's show this in database language. This is our original (source) DDL+DML:

`IF EXISTS(``select` `OBJECT_ID(``'dbo.MyTbl'``, ``'U'``)) ``DROP` `TABLE` `dbo.MyTbl`
`GO`
` `
`create` `table` `MyTbl (`
`      ``start_date datetime`
`    ``, end_date datetime`
`    ``, volumes ``int`
`)`
`GO`
` `
`insert` `MyTbl`
`select` `'20140310'``,``'20140310'``,100 ``union` `all`
`select` `'20130310'``,``'20140310'``,244 ``union` `all`
`select` `'20120310'``,``'20140310'``,222 ``union` `all`
`select` `'20140210'``,``'20140210'``,456 ``union` `all`
`select` `'20140210'``,``'20140310'``,3333`
`GO`
` `
`select` `* ``from` `MyTbl`
`GO`

We can see that the first record include only 1 day of working, therefor in the result set we will get 1 record. But the second record include a full year of working, therefor this record should split into 12 months included in the date rang, and each month should get number of average working hours relative to the numbers of days in that month. The third record include 3 months... and so on...

## Solutions and Explanation

In our solution we are going to based on finding each starting date and ending date of the month. In order to do this we will use this logic (You can use different logic for this):

`-- get first+last day in the month`
`declare` `@SDate datetime = ``'20130210'`
`SELECT`
`    ``DATEADD(mm, DATEDIFF(mm,0,@SDate), 0),`
`    ``DATEADD(``DAY``,-1,DATEADD(mm, DATEDIFF(mm,0,@SDate)+1, 0))`
`GO`

We will start with a solution for splitting a single date range, which can be use for creating a function.

`declare` `@SDate datetime = ``'20140310'``, @EDate datetime = ``'20140615'`
`select` `FirstDayOfMonth,LastDayOfMonth,DATEDIFF(``DAY``,FirstDayOfMonth,LastDayOfMonth)+1 [Number ``Of` `Days This ``month``]`
`from` `(`
`    ``select` `top` `100`
`        ``FirstDayOfMonth = ``CASE`
`            ``when` `DATEADD(``MONTH``, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,@SDate)), 0) < @SDate`
`                ``then` `@SDate`
`            ``else`
`                ``DATEADD(``MONTH``, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,@SDate)), 0)`
`         ``end`
`        ``, LastDayOfMonth = ``CASE`
`            ``when`
`            ``DATEADD(``DAY``,-1,DATEADD(mm, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,@SDate))+1, 0)) > @EDate`
`                ``then` `@EDate`
`            ``else`
`                ``DATEADD(``DAY``,-1,DATEADD(mm, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,@SDate))+1, 0))`
`         ``end`
`    ``from` `_ArielyAccessoriesDB.dbo.Numbers`
`    ``where`
`        ``DATEADD(mm, DATEDIFF(mm,0,DATEADD(``MONTH``,N,@SDate)), 0) < @EDate`
`        ``-- without this filter you will get error`
`        ``-- "Adding a value to a 'datetime' column caused an overflow"`
`        ``and` `N < 1000`
`) T`
`GO`

Now let's move to a more complex solution without using a function for each record. In this solution we will use OUTER APPLY in order to work on the entire table as SET.

`select` `start_date,end_date,volumes,FirstDayOfMonth,LastDayOfMonth`
`from` `MyTbl V`
`OUTER` `APPLY`
`(`
`    ``select` `top` `1000`
`         ``FirstDayOfMonth = ``CASE`
`            ``when` `DATEADD(``MONTH``, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,V.start_date)), 0) < V.start_date`
`                ``then` `V.start_date`
`            ``else`
`                ``DATEADD(``MONTH``, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,V.start_date)), 0)`
`         ``end`
`        ``, LastDayOfMonth = ``CASE`
`            ``when`
`            ``DATEADD(``DAY``,-1,DATEADD(mm, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,V.start_date))+1, 0)) > V.end_date`
`                ``then` `V.end_date`
`            ``else`
`                ``DATEADD(``DAY``,-1,DATEADD(mm, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,V.start_date))+1, 0))`
`         ``end`
`    ``from` `_ArielyAccessoriesDB.dbo.Numbers`
`) T`
`where` `FirstDayOfMonth <= V.end_date`

Next we need to use our splitting solution in order to get the Average working time for each month.

`select`
`    ``start_date,`
`    ``end_date,`
`    ``volumes,`
`    ``FirstDayOfMonth,`
`    ``LastDayOfMonth,`
`    ``DATEDIFF(``DAY``,FirstDayOfMonth,LastDayOfMonth)+1 ``as` `NumberOfDaysThisMonth,`
`    ``DATEDIFF(``DAY``,start_date,end_date) + 1 TotalNumberOfDays,`
`    ``(  ``CONVERT``(``float``,DATEDIFF(``DAY``,FirstDayOfMonth,LastDayOfMonth)+1)/(DATEDIFF(``DAY``,start_date,end_date) + 1)  ) * volumes ``as` `volumesPerMonth`
`from` `MyTbl V`
`OUTER` `APPLY`
`(`
`    ``select` `top` `1000`
`         ``FirstDayOfMonth = ``CASE`
`            ``when` `DATEADD(``MONTH``, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,V.start_date)), 0) < V.start_date`
`                ``then` `V.start_date`
`            ``else`
`                ``DATEADD(``MONTH``, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,V.start_date)), 0)`
`         ``end`
`        ``, LastDayOfMonth = ``CASE`
`            ``when`
`            ``DATEADD(``DAY``,-1,DATEADD(mm, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,V.start_date))+1, 0)) > V.end_date`
`                ``then` `V.end_date`
`            ``else`
`                ``DATEADD(``DAY``,-1,DATEADD(mm, DATEDIFF(``MONTH``,0,DATEADD(``MONTH``,N,V.start_date))+1, 0))`
`         ``end`
`    ``from` `_ArielyAccessoriesDB.dbo.Numbers`
`) T`
`where` `FirstDayOfMonth <= V.end_date`

.

## Summary

In this article we show how we can split a date range into a set of monthly records.

• The code used numbers table _ArielyAccessoriesDB.dbo.Numbers
This is highly recommended to have a numbers table in the database, or in general read only accessories database. This table is indexed using clustered index and will give us better solution then build it on-the-fly in each query. If you don't have one, please build a number table.
• Guy Glantser, commented and remind us that using SQL Server version 2012/2014 we can use the function EOMONTH, Thanks.
• SaidaKishore Potla, commented that we can use case with between operator, Thanks
* You are most welcome to comment and suggest improvements, using the facebook.
I posted an announcement about the new blog, in my Facebook page, as I usually do.
* You can follow my Facebook page if you want to get my announcment fast :-)