SQL split number over date range

Question

I have a table with, for example this data

ID |start_date  |end_date   |amount
---|------------|-----------|-------
a1 |2013-12-01  |2014-03-31 |100

Iwant to have a query that split the dates so I have the amount splitted out over the year like this :

ID |org_start_date  |org_end_date   |new_start_date  |new_end_date    |amount
---|----------------|---------------|----------------|----------------|-------
a1 |2013-12-01      |2014-03-31     |2013-12-01      |2013-12-31      |25
a1 |2013-12-01      |2014-03-31     |2014-01-01      |2014-03-31      |75

The 25 in 2013 is because 2013 has one month and 75 in 2014 because this has 3 months

Is there a way to do this in T-SQL?

Thx in advance!

Split by year? Do you need to split the start and end date in one year? — Jaugar Chang, Oct 23 '14 at 08:31
@DavidG by months and split values over months, so it's 4 months, 100/4 = monthly amount, then add month by year — Tanner, Oct 23 '14 at 08:31
And how do you know to give 25 to the first month and then the 2nd row is 3 months? — DavidG, Oct 23 '14 at 08:33
amount is not the problem, I just can do that with an datediff and calculate the amount for a day. It's more the split based upon years — NCS, Oct 23 '14 at 08:34
Will there only ever be two years (hence two rows involved) or could you have 1 or 3 years/rows or more? — bendataclear, Oct 23 '14 at 08:39

score 2 · Answer 1 · answered Oct 23 '14 at 09:20

Here is a solution using a numbers table:

SQL Fiddle Example

DECLARE @STARTYR INT = (SELECT MIN(YEAR([Start Date])) FROM Table1)
DECLARE @ENDYR INT = (SELECT MAX(YEAR([End Date])) FROM Table1)

SELECT [Id]
     , @STARTYR + Number AS [Year]
     , CASE WHEN YEAR([Start Date]) < @STARTYR + Number 
            THEN DATEADD(YEAR, @STARTYR - 1900 + Number,0) 
            ELSE [Start Date] END AS [Start]
     , CASE WHEN YEAR([End Date]) > @STARTYR + Number 
            THEN DATEADD(YEAR, @STARTYR - 1900 + Number + 1,0) 
            ELSE [End Date] END AS [End]
     , DATEDIFF(MONTH,CASE WHEN YEAR([Start Date]) < @STARTYR + Number 
                           THEN DATEADD(YEAR, @STARTYR - 1900 + Number,0) 
                           ELSE [Start Date] END
                     ,CASE WHEN YEAR([End Date]) > @STARTYR + Number 
                           THEN DATEADD(YEAR, @STARTYR - 1900 + Number + 1,0) 
                           ELSE DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(MONTH,1,[End Date])),0) END) AS [Months]
     , DATEDIFF(MONTH,[Start Date],[End Date]) + 1 [Total Months]
     , ([Amount] / (DATEDIFF(MONTH,[Start Date],[End Date]) + 1)) 
       *
       DATEDIFF(MONTH,CASE WHEN YEAR([Start Date]) < @STARTYR + Number 
                           THEN DATEADD(YEAR, @STARTYR - 1900 + Number,0) 
                           ELSE [Start Date] END
                     ,CASE WHEN YEAR([End Date]) > @STARTYR + Number 
                           THEN DATEADD(YEAR, @STARTYR - 1900 + Number + 1,0) 
                           ELSE DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(MONTH,1,[End Date])),0) END) AS [Proportion]

FROM Numbers
LEFT JOIN Table1 ON YEAR([Start Date]) <= @STARTYR + Number
                 AND YEAR([End Date]) >= @STARTYR + Number


WHERE Number <= @ENDYR - @STARTYR

score 2 · Accepted Answer · answered Oct 23 '14 at 09:31

2

Use spt_values table to create a calendar table, then join to your table to split date range into any part you want.

If split by year and divide amount by months you could:

with dates as
(
select number,DATEADD(day,number,'20130101') as dt
    from master..spt_values
    where number between 0 and 1000 AND TYPE='P'
)
select
    m.start_date as org_start_date,
    m.end_date as org_end_date,
    min(d.dt) as new_start_date,
    max(d.dt) as new_end_date,
    m.amount*count(distinct month(d.dt))/(datediff(month,m.start_date,m.end_date)+1) as amount
from 
    MonthSplit m
join
    dates d
on 
    d.dt between m.start_date and m.end_date
group by 
    m.start_date, m.end_date, year(d.dt),m.amount

Here is the SQL FIDDLE DEMO.

answered Oct 23 '14 at 09:31

Jaugar Chang

3,176
2
13
23

unfortunately this doesn't work when start and end have more than one year between. For example 2011 and 2014 – NCS Oct 23 '14 at 11:59
You could change the number 1000 to what you need. Here is the demo(http://sqlfiddle.com/#!6/d61051/1) – Jaugar Chang Oct 23 '14 at 12:01
Here is a link about numbers table(http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx). There are more ways of creating numbers table you could learn in the comments . – Jaugar Chang Oct 23 '14 at 12:06

score 0 · Answer 3 · edited May 23 '17 at 12:27

It's very similar to this question:

Split date range into one row per month in sql server

Although you are doing grouping by year, so based on that answer you can modify it to do what you want by adding MIN, MAX to your date values and grouping by the YEAR():

SQL Fiddle Demo

Schema Setup:

CREATE TABLE MonthSplit
    ([ID] varchar(2), [start_date] datetime, [end_date] datetime, [amount] int)
;

INSERT INTO MonthSplit
    ([ID], [start_date], [end_date], [amount])
VALUES
    ('a1', '2013-12-01 00:00:00', '2014-03-31 00:00:00', 100),
    ('a2', '2013-10-01 00:00:00', '2015-05-01 00:00:00', 400)
;

Recursive CTE to group by Year:

WITH cte AS
(SELECT ID
      , start_date
      , end_date
      , start_date AS from_date
      , DATEADD(day, day(start_date)* -1 + 1, start_date) AS first_of_month
 FROM MonthSplit
 UNION ALL
 SELECT ID
     , start_date
     , end_date
     , DATEADD(month,1,first_of_month)
     , DATEADD(month,1,first_of_month)
  FROM cte
  WHERE DATEADD(month,1,from_date) < end_date
)
SELECT ID as ID, 
       min(start_date) as org_start_date, 
       min(end_date) as org_end_date, 
       min(from_date) AS new_start_date,
       CASE when max(end_date) < DATEADD(month,1,max(first_of_month)) THEN
           max(end_date)
       ELSE
           DATEADD(day, -1, DATEADD(month,1,max(first_of_month)))
       END AS new_end_date
FROM cte
group by year(from_date), ID

Results:

| ID | ORG_START_DATE    | ORG_END_DATE   | NEW_START_DATE    | NEW_END_DATE      |
|----|-------------------|----------------|-------------------|-------------------|
| a1 | December, 01 2013 | March, 31 2014 | December, 01 2013 | December, 31 2013 |
| a1 | December, 01 2013 | March, 31 2014 | January, 01 2014  | March, 31 2014    |
| a2 |  October, 01 2013 | May, 01 2015   | October, 01 2013  | December, 31 2013 |
| a2 |  October, 01 2013 | May, 01 2015   | January, 01 2014  | December, 31 2014 |
| a2 |  October, 01 2013 | May, 01 2015   | January, 01 2015  | April, 30 2015    |

Actually I think it's with more than one row not multiple years: http://sqlfiddle.com/#!6/019c1/1/0 — bendataclear, Oct 23 '14 at 09:15
unfortunatly this gives a recursionerror. Think my dates are to far apart.. "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." — NCS, Oct 23 '14 at 09:52

DataHacker · Answer 4 · 2014-10-23T09:15:55.703

I don't have a ready made SQL for you but just a thought to solve this problem. If you have some experience with SQL it won't be hard to express it in SQL.

You could do this by defining a reference table for the months that are between your begin and end date:

ID    |   month       |   year  |   month start   |  month end    |   count |
-----------------------------------------------------------------------------
1001  |   dec-2013    |   2013  |   1-12-2013     |   31-12-2013  |     1   |
1001  |   jan-2014    |   2014  |   1-1-2014      |   31-1-2014   |     1   |
1001  |   feb-2014    |   2014  |   1-2-2014      |   28-2-2014   |     1   |
1001  |   mar-2014    |   2014  |   1-3-2014      |   31-3-2014   |     1   |

Maybe you already have such a time ref table in your DWH.

When you join (with a between statement) your table with the record that contains the start and end date per row, with this reference table, you'll have the split from 1 row to the number of months contained in the range correct. The count column will help you to get the split ratio per year right by grouping over the year afterwards (like : 1/4 for 2013 and 3/4 for 2014). You'll have to apply the ratio to the field 'amount' that you want to split up.

SQL split number over date range

4 Answers4

SQL Fiddle Demo

Linked