CODEWITHSUNDEEP
sql-server
scapegoat17
scapegoat17

Reputation: 5831

Script to populate Date Dimension table for next 50 years

I have a Dimension table that I will need to display the YYYYMM for over the next 50 years. I am sure that there is a way to do this without manually entering the data, but I am not sure the appropriate way to do so.

My table has 

An ID which is an int that will display YYYYMM
the month that will be a varchar(2) to display MM
and the year that will be a varchar(4) to display YYYY

Would anyone be able to help me out with this? I have never really had to write a script before and the examples I have seen online are more showing with datetime data types.

I would also probably like to start a few years back for already existing data. Let's say 2010 - 2070

Upvotes: 0

Views: 2100

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93744

I will do this using tally table with stacked cte. Try this

declare @start_year varchar(4)='2010',@end_year varchar(4)='2070'

;WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e2 CROSS JOIN e2 AS b), -- 100*100
e4(n) AS (SELECT 1 FROM e3 CROSS JOIN (SELECT TOP 5 n FROM e1) AS b)  -- 5*10000
,e5 as (SELECT Dateadd(Month, Row_number()OVER (
                                  ORDER BY n) - 1, Cast(@start_year + '-01-01' AS DATE)) AS dates
        FROM   e4)

       SELECT Id=LEFT(CONVERT(CHAR(8), dates, 112), 6),
              [Month]=Month(dates),
              [Year]=Year(dates)
       FROM   e5
       WHERE  Year(dates) <= @end_year

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

This works for me

DECLARE @t TABLE
    (
      id INT ,
      m VARCHAR(2) ,
      y VARCHAR(4)
    )

DECLARE @i INT = 1
DECLARE @today DATE = '20150121'
DECLARE @endDate DATE = DATEADD(YEAR, 50, @today)

WHILE @today <= @endDate
    BEGIN

        INSERT  INTO @t
                ( id ,
                  m ,
                  y
                )
        VALUES  ( CAST(LEFT(CONVERT(CHAR(8), @today, 112), 6) AS INT) ,
                  SUBSTRING(CONVERT(CHAR(8), @today, 112), 5, 2) ,
                  LEFT(CONVERT(CHAR(8), @today, 112), 4)
                )

        SET @today = DATEADD(MONTH, 1, @today)
    END

Output:

id  m   y
201501  01  2015
201502  02  2015
201503  03  2015
201504  04  2015
201505  05  2015
201506  06  2015
201507  07  2015
201508  08  2015
201509  09  2015
201510  10  2015
201511  11  2015
201512  12  2015
201601  01  2016
201602  02  2016
201603  03  2016
      . . .
206410  10  2064
206411  11  2064
206412  12  2064
206501  01  2065

Upvotes: 1

Related Questions