SQL – Date Range Broken Down by Individual Month and Year while maintaining the ID

date

I need help breaking down a date range, from an existing table, into the individual month and
year of the date range while maintaining the Identification Number
assigned to that date range. Thanks!

CREATE TABLE #TEMP
(GRP_ID CHAR(8),
BILL_YR INT,
BEGIN_DT DATETIME,
END_DT DATETIME
)

INSERT INTO #TEMP
(GRP_ID, BILL_YR, BEGIN_DT, END_DT)
VALUES
('12345678','1','2019-01-01','2019-12-31'),
('12345678','2','2020-01-01','2020-12-31')

EXPECTED RESULT

GRP_ID      BILL_YR  BEGIN_DT   END_DT
12345678    1        1          2019
12345678    1        2          2019
12345678    1        3          2019
12345678    1        4          2019
12345678    1        5          2019
12345678    1        6          2019
12345678    1        7          2019
12345678    1        8          2019
12345678    1        9          2019
12345678    1        10         2019
12345678    1        11         2019
12345678    1        12         2019
12345678    2        1          2020
12345678    2        2          2020
12345678    2        3          2020
12345678    2        4          2020
12345678    2        5          2020
12345678    2        6          2020
12345678    2        7          2020
12345678    2        8          2020
12345678    2        9          2020
12345678    2        10         2020
12345678    2        11         2020
12345678    2        12         2020

Best Answer

You can join against a date dimension table. I've included a sample Common Table Expression date dimension table - (my sample is for months instead of days).

To create a permanent Date Dimension table (also known as a calendar table), check out this post - Creating a date dimension or calendar table in SQL Server

--demo setup
CREATE TABLE #TEMP
(GRP_ID CHAR(8),
BILL_YR INT,
BEGIN_DT DATETIME,
END_DT DATETIME
)

INSERT INTO #TEMP
(GRP_ID, BILL_YR, BEGIN_DT, END_DT)
VALUES
('12345678','1','2019-01-01','2019-12-31'),
('12345678','2','2020-01-01','2020-12-31')

--common table expression date dimension
;with dates ([Date]) as (
    Select convert(date,'2019-01-01') as [Date] -- Put the start date here

    union all 

    Select dateadd(month, 1, [Date])
    from dates
    where [Date] <= '2020-12-31' -- Put the end date here 
)

select GRP_ID
    ,  BILL_YR
    ,  DATEPART(MONTH,[DATE]) as BEGIN_DT
    ,  DATEPART(YEAR,[DATE]) as END_DT
    from dates d
    join #temp t on d.Date >= t.BEGIN_DT
        and d.Date <= t.END_DT
option (maxrecursion 32767) -- Don't forget to use the maxrecursion option!

| GRP_ID   | BILL_YR | BEGIN_DT | END_DT |
|----------|---------|----------|--------|
| 12345678 | 1       | 1        | 2019   |
| 12345678 | 1       | 2        | 2019   |
| 12345678 | 1       | 3        | 2019   |
| 12345678 | 1       | 4        | 2019   |
| 12345678 | 1       | 5        | 2019   |
| 12345678 | 1       | 6        | 2019   |
| 12345678 | 1       | 7        | 2019   |
| 12345678 | 1       | 8        | 2019   |
| 12345678 | 1       | 9        | 2019   |
| 12345678 | 1       | 10       | 2019   |
| 12345678 | 1       | 11       | 2019   |
| 12345678 | 1       | 12       | 2019   |
| 12345678 | 2       | 1        | 2020   |
| 12345678 | 2       | 2        | 2020   |
| 12345678 | 2       | 3        | 2020   |
| 12345678 | 2       | 4        | 2020   |
| 12345678 | 2       | 5        | 2020   |
| 12345678 | 2       | 6        | 2020   |
| 12345678 | 2       | 7        | 2020   |
| 12345678 | 2       | 8        | 2020   |
| 12345678 | 2       | 9        | 2020   |
| 12345678 | 2       | 10       | 2020   |
| 12345678 | 2       | 11       | 2020   |
| 12345678 | 2       | 12       | 2020   |
Related Question