SQL: Generate Record Per Month In Date Range

Question

I have a table which describes a value which is valid for a certain period of days / months. The table looks like this:

+----+------------+------------+-------+
| Id |    From    |     To     | Value |
+----+------------+------------+-------+
|  1 | 2018-01-01 | 2018-03-31 | ValA  |
|  2 | 2018-01-16 | NULL       | ValB  |
|  3 | 2018-04-01 | 2018-05-12 | ValC  |
+----+------------+------------+-------+

As you can see, the only value still valid on this day is ValB (To is nullable, From isn't).

I am trying to achieve a view on this table like this (assuming I render this view someday in july 2018):

+----------+------------+------------+-------+
| RecordId |    From    |     To     | Value |
+----------+------------+------------+-------+
|        1 | 2018-01-01 | 2018-01-31 | ValA  |
|        1 | 2018-02-01 | 2018-02-28 | ValA  |
|        1 | 2018-03-01 | 2018-03-31 | ValA  |
|        2 | 2018-01-16 | 2018-01-31 | ValB  |
|        2 | 2018-02-01 | 2018-02-28 | ValB  |
|        2 | 2018-03-01 | 2018-03-31 | ValB  |
|        2 | 2018-04-01 | 2018-04-30 | ValB  |
|        2 | 2018-05-01 | 2018-05-31 | ValB  |
|        2 | 2018-06-01 | 2018-06-30 | ValB  |
|        3 | 2018-04-01 | 2018-04-30 | ValC  |
|        3 | 2018-05-01 | 2018-05-12 | ValC  |
+----------+------------+------------+-------+

This view basically creates a record for each record in the table, but splitted by month, using the correct dates (especially minding the start and end dates that are not on the first or the last day of the month). The one record without a To date (so it's still valid to this day), is rendered until the last day of the month in which I render the view, so at the time of writing, this is july 2018.

This is a simple example, but a solution will seriously help me along. I'll need this for multiple calculations, including proration of amounts.

Here's a table script and some insert statements that you can use:

CREATE TABLE [dbo].[Test]
(
    [Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [From] SMALLDATETIME NOT NULL,
    [To] SMALLDATETIME NULL,
    [Value] NVARCHAR(100) NOT NULL
)

INSERT INTO dbo.Test ([From],[To],[Value])
VALUES 
('2018-01-01','2018-03-31','ValA'),
('2018-01-16',null,'ValB'),
('2018-04-01','2018-05-12','ValC');

Thanks in advance!

Answer 1

Generate all months that might appear on your values (with start and end), then join where each month overlaps the period of your values. Change the result so if a month doesn't overlap fully, you just display the limits of your period.

DECLARE @StartDate DATE = '2018-01-01'
DECLARE @EndDate DATE = '2020-01-01'

;WITH GeneratedMonths AS
(
    SELECT
        StartDate = @StartDate,
        EndDate = EOMONTH(@StartDate)
    UNION ALL
    SELECT
        StartDate = DATEADD(MONTH, 1, G.StartDate),
        EndDate = EOMONTH(DATEADD(MONTH, 1, G.StartDate))
    FROM
        GeneratedMonths AS G
    WHERE
        DATEADD(MONTH, 1, G.StartDate) < @EndDate
)
SELECT
    T.Id,
    [From] = CASE WHEN T.[From] >= G.StartDate THEN T.[From] ELSE G.StartDate END,
    [To] = CASE WHEN G.EndDate >= T.[To] THEN T.[To] ELSE G.EndDate END,
    T.Value
FROM
    dbo.Test AS T
    INNER JOIN GeneratedMonths AS G ON
        G.EndDate >= T.[From] AND
        G.StartDate <= ISNULL(T.[To], GETDATE())
ORDER BY
    T.Id,
    G.StartDate
OPTION
    (MAXRECURSION 3000)