Split date range into one row per month in SQL Server pt2

Question

This question relates to this post: Split date range into one row per month in sql server

But I'm new to Stackoverflow and new users are not allowed to place comments to provided solutions.That's why I am now adding this as a new question.

The question was this:

I have a table with two column called "from_date" and "to_date"

The table looks like this:

Table (I am not allowed to embed my picture here, so please click the link)

I want result like:-

from_date || to_date
----------- ------------

2013-11-25 || 2013-11-30

2013-12-01 || 2013-12-05

That date is splits from 2013-11-25 to 2013-11-30 and another date split from 2013-12-01 to 2013-12-05... Is it possible to split like this ?

Aaron Bertrand's solution is this:

DECLARE @d TABLE(from_date DATE, to_date DATE);

INSERT @d VALUES ('2013-11-25','2013-12-05');

;WITH n(n) AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 
    FROM sys.all_columns
),
d(n,f,t,md,bp,ep) AS 
(
    SELECT n.n, d.from_date, d.to_date, 
           DATEDIFF(MONTH, d.from_date, d.to_date),
           DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY(from_date), from_date)),
           DATEADD(MONTH, n.n+1, DATEADD(DAY, 0-DAY(from_date), from_date))
    FROM n 
    INNER JOIN @d AS d ON d.to_date >= DATEADD(MONTH, n.n-1, d.from_date)
)
SELECT original_from_date = f, original_to_date = t, 
       new_from_date = CASE n WHEN 0  THEN f ELSE bp END,
       new_to_date   = CASE n WHEN md THEN t ELSE ep END 
FROM d 
WHERE md >= n
ORDER BY original_from_date, new_from_date;

Results:

original_from_date   original_to_date   new_from_date   new_to_date
------------------   ----------------   -------------   -----------
2013-11-25           2013-12-05         2013-11-25      2013-11-30
2013-11-25           2013-12-05         2013-12-01      2013-12-05

Aaron Bertrand's answer is a super powerful query, but it seems it only works well if the startdate is in a month following a month with 31 days. If you change the startdate to, for example, 2013-5-25 (following April, with only 30 days), the new_to_date values are incorrect: all full months suddenly get a max of 30 days then:

original_from_date  original_to_date    new_from_date   new_to_date
2013-05-25          2013-12-05          2013-05-25      2013-05-30
2013-05-25          2013-12-05          2013-06-01      2013-06-30
2013-05-25          2013-12-05          2013-07-01      2013-07-30
2013-05-25          2013-12-05          2013-08-01      2013-08-30
2013-05-25          2013-12-05          2013-09-01      2013-09-30
2013-05-25          2013-12-05          2013-10-01      2013-10-30
2013-05-25          2013-12-05          2013-11-01      2013-11-30
2013-05-25          2013-12-05          2013-12-01      2013-12-05

Can anyone edit the query so that end dates are correct (28,30,31 days, and 29 feb leap) regardless of what month the start date is in? I can't figure out how to do it.

Many thanks, Martijn Vermunt

Welcome to SO. Please read [ask] and post a full question. Just referring to other questions is not helpful. — Amit, Dec 29 '15 at 15:50
Thanks Amit. I totally agree: I wish I could just place a comment to the original post, that would make it a lot easier indeed — Martijn Vermunt, Dec 29 '15 at 15:56
Alright done. Included the full original question and solution now. Thanks — Martijn Vermunt, Dec 29 '15 at 16:15
Try Robert Co's solution http://stackoverflow.com/a/20272758/2471473 — Kennah, Dec 29 '15 at 16:17
Thanks @Kennah Robert Co's solution stackoverflow.com/a/20272758/2471473 doesn't do the trick every time. With dates >INSERT @d VALUES ('2013-11-25','2013-12-05'); does not provide the December part in the result set. — Martijn Vermunt, Dec 29 '15 at 16:23
Sorry about the issue with non-31-day-months. [I've fixed my query](http://stackoverflow.com/a/20271267/61305), so, if it resolves the issue for you in all cases, this question could likely be closed as a duplicate. — Aaron Bertrand, Dec 29 '15 at 16:50
Fantastic work! Thanks a million, your query is awesome.This question can be closed indeed — Martijn Vermunt, Dec 30 '15 at 08:26

score 0 · Answer 1 · edited May 23 '17 at 11:52

The answer has been provided, the query has been edited and made perfect. Thank you @Aaron Bertrand for updating the query!

DECLARE @d TABLE(from_date DATE, to_date DATE);

INSERT @d VALUES ('2013-11-25','2013-12-05');

;WITH n(n) AS 
(
SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns
),
d(n,f,t,md,bp,ep) AS  
(
SELECT n.n, d.from_date, d.to_date, 
DATEDIFF(MONTH, d.from_date, d.to_date),
DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY(from_date), from_date)),
DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n, 
 DATEADD(DAY, 1-DAY(from_date), from_date))))
FROM n INNER JOIN @d AS d 
ON d.to_date >= DATEADD(MONTH, n.n-1, d.from_date)
)
SELECT original_from_date = f, original_to_date = t, 
new_from_date = CASE n WHEN 0  THEN f ELSE bp END,
new_to_date   = CASE n WHEN md THEN t ELSE ep END 
FROM d WHERE md >= n
ORDER BY original_from_date, new_from_date;

Split date range into one row per month in SQL Server pt2

1 Answers1

Linked