0

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

1 Answer 1

1

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   |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.