Generating Sequential Months in SQL

Question

I have a table like below sample:

code |  date       | value
1000   2016-08-05    5000
1000   2016-12-27    8000
1000   2018-03-19    6000
1000   2018-06-02    6000

Now I need to generate sequential months like this:

code |  date       | value
1000   2016-08-05    5000
1000   2016-09-05    5000
1000   2016-10-05    5000
1000   2016-11-05    5000
1000   2016-12-27    8000
1000   2017-01-27    8000
1000   2017-02-27    8000
........
1000   2018-03-19    6000
1000   2018-04-19    6000
1000   ....

The sequence continues until it reaches the max date of a code. In this example the max date is 2018-06-02 for code 1000. How can I generate the month sequence? Any help would be much appreciated.

Please read the first paragraph of the [sql tag info](https://stackoverflow.com/tags/sql/info) and [edit] your question accordingly. — Zohar Peled, Nov 05 '19 at 08:07
What @ZoharPeled is saying, update your question with SQL examples of what you have already tried and what isn't working about it. — Jordy van Eijk, Nov 05 '19 at 08:14

score 3 · Answer 1 · answered Nov 05 '19 at 08:15

Try this:

DECLARE @DataSource TABLE
(
    [Code] INT
   ,[Date] DATE
   ,[value] INT
);

INSERT INTO @DataSource ([Code], [Date], [value])
VALUES (1000, '2016-08-05', 5000)
      ,(1000, '2016-12-27', 8000)
      ,(1000, '2018-03-19', 6000)
      ,(1000, '2018-06-02', 6000);

WITH Ranges AS
(
    SELECT *
          ,LEAD([Date]) OVER (ORDER BY [Date] ASC) AS [DateEnd]
    FROM @DataSource DS
)
SELECT *
FROM Ranges
CROSS APPLY
(
    SELECT DATEADD(MONTH, [number], [Date])
    FROM 
    (
        select number 
        from master.dbo.spt_values
        where [type] = 'P'
    ) numbers
    WHERE DATEADD(MONTH, [number], [Date]) < [DateEnd]
) AutoDates ([GeneratedDate]);

The idea is simple - using LEAD get the upper bound of the date range. Then having start and end date, just generate the missing months using the DATEADD function.

score 2 · Answer 2 · answered Nov 05 '19 at 08:24

One solution would be create a table of numbers in a recursive CTE, and then join with the table. You can use a not exists join condition to define the boundaries of each series: I understand that you want only one date per month (no gaps and no overlaps on the same month), so I used eomonth() for that.

Query:

with nums(i) as (
    select 0
    union all select i + 1 from nums where i < 24
)
select 
    t.code,
    dateadd(month, i, t.date) date,
    t.value
from mytable t
inner join nums n 
    on not exists (
        select 1
        from mytable t1
        where t1.date > t.date and eomonth(t1.date) < eomonth(dateadd(month, i + 1, t.date))
    )
order by date

You can extend the upper limit in the cte to the maximum number of consecutive months (I set it to 24).

Demo on DB Fiddle:

code | date                | value
---: | :------------------ | ----:
1000 | 05/08/2016 00:00:00 |  5000
1000 | 05/09/2016 00:00:00 |  5000
1000 | 05/10/2016 00:00:00 |  5000
1000 | 05/11/2016 00:00:00 |  5000
1000 | 27/12/2016 00:00:00 |  8000
1000 | 27/01/2017 00:00:00 |  8000
1000 | 27/02/2017 00:00:00 |  8000
1000 | 27/03/2017 00:00:00 |  8000
1000 | 27/04/2017 00:00:00 |  8000
1000 | 27/05/2017 00:00:00 |  8000
1000 | 27/06/2017 00:00:00 |  8000
1000 | 27/07/2017 00:00:00 |  8000
1000 | 27/08/2017 00:00:00 |  8000
1000 | 27/09/2017 00:00:00 |  8000
1000 | 27/10/2017 00:00:00 |  8000
1000 | 27/11/2017 00:00:00 |  8000
1000 | 27/12/2017 00:00:00 |  8000
1000 | 27/01/2018 00:00:00 |  8000
1000 | 27/02/2018 00:00:00 |  8000
1000 | 19/03/2018 00:00:00 |  6000
1000 | 19/04/2018 00:00:00 |  6000
1000 | 19/05/2018 00:00:00 |  6000
1000 | 02/06/2018 00:00:00 |  6000
1000 | 02/07/2018 00:00:00 |  6000
1000 | 02/08/2018 00:00:00 |  6000
1000 | 02/09/2018 00:00:00 |  6000
1000 | 02/10/2018 00:00:00 |  6000
1000 | 02/11/2018 00:00:00 |  6000
1000 | 02/12/2018 00:00:00 |  6000
1000 | 02/01/2019 00:00:00 |  6000
1000 | 02/02/2019 00:00:00 |  6000
1000 | 02/03/2019 00:00:00 |  6000
1000 | 02/04/2019 00:00:00 |  6000
1000 | 02/05/2019 00:00:00 |  6000
1000 | 02/06/2019 00:00:00 |  6000
1000 | 02/07/2019 00:00:00 |  6000
1000 | 02/08/2019 00:00:00 |  6000
1000 | 02/09/2019 00:00:00 |  6000
1000 | 02/10/2019 00:00:00 |  6000
1000 | 02/11/2019 00:00:00 |  6000
1000 | 02/12/2019 00:00:00 |  6000
1000 | 02/01/2020 00:00:00 |  6000
1000 | 02/02/2020 00:00:00 |  6000
1000 | 02/03/2020 00:00:00 |  6000
1000 | 02/04/2020 00:00:00 |  6000
1000 | 02/05/2020 00:00:00 |  6000
1000 | 02/06/2020 00:00:00 |  6000

Generating Sequential Months in SQL

2 Answers2