Run TSQL script on Redshift

Question

I have a script that I once created and have been using on TSQL for years. It creates a calendar dimension.

I converted my script from T-SQL to Redshift using http://www.sqlines.com/online. I executed it through DBeaver. It throws all kinds of errors.

I tried correcting it but then found out Redshift does not support variables.

Does anyone know whether (and if so, how) I can execute the script below on Redshift? The script below is pure T-SQL, do not hesitate to use it, change it, improve it out- or inside the context of this question.

Parts of it are dutch but using the translations below you should be easily able to understand what it is doing

dag = day
maand = month
jaar = year
werkdag = workingday
lang = long
kort = short
teller = counter
naam = name
feestdag = holiday

set nocount on

declare @begindatum as datetime = dateadd(year,-150,getdate())
declare @aantal_jaren as int = 1 -- betekent x - 100 jaren in de toekomst
declare @einddatum as datetime = dateadd(year,@aantal_jaren,@begindatum)
declare @rondedatum as datetime = @begindatum
declare @print as varchar(max)
set language dutch


select 'Start', getdate()




Declare @kalender as table (
[PeriodeID] [int] NOT NULL,
[Datum] [date] NOT NULL,
[DatumMedium] [varchar](25) NULL,
[DatumLang] [varchar](25) NULL,
[DatumExtraLangNOT] [varchar](25) NULL,
[DatumVoluit] [varchar](50) NULL,
[DagVanJaar] [int] NULL,
[DagVanWeek] [int] NULL,
[DagVanMaand] [int] NULL,
[DagNaamKort] [nvarchar](4000) NULL,
[DagNaamLang] [nvarchar](30) NULL,
[WerkDag] [int] NULL,
[Weekend] [int] NOT NULL,
[Feestdag] [varchar](25) NULL,
[Werkdagteller] [int] NOT NULL,
[WeekNr] [int] NULL,
[Iso_WeekNr] [int] NULL,
[Maand] [int] NULL,
[MaandNaamKort] [nvarchar](30) NULL,
[MaandNaamLang] [nvarchar](4000) NULL,
[Kwartaal] [int] NULL,
[KwartaalNaamKort] [varchar](2) NULL,
[KwartaalNaamLang] [varchar](25) NULL,
[Jaar] [int] NOT NULL,
[JaarKwartaal] int NULL,
[JaarKwartaalNaam] [varchar](7) NULL,
[JaarWeek] [int] NULL,
[JaarWeekNaam] [varchar](25) NULL,
[JaarIso_Week] [int] NULL,
[JaarIso_WeekNaam] [varchar](25) NULL,
[JaarMaand] [int] NULL,
[JaarMaandNaamKort] [varchar](25) NULL,
[JaarMaandNaamMedium] [varchar](25) NULL,
[JaarMaandNaamLang] [varchar](25) NULL,
PRIMARY KEY (PeriodeID)
)


While @rondedatum <= @einddatum
BEGIN

Insert into @kalender
SELECT
cast(@rondedatum as int) as PeriodeID
,cast(@rondedatum as date) as Datum
,cast(DATEPART ( Day , @rondedatum ) as varchar(2)) + ' ' + Format(@rondedatum,'MMM') + ' '+ cast(year(@rondedatum) as varchar(4)) as DDMMMJJJJ
,cast(DATEPART ( Day , @rondedatum ) as varchar(2)) + ' ' + Format(@rondedatum,'MMMM') + ' '+ cast(year(@rondedatum) as varchar(4)) as DatumLang
,Format(@rondedatum,'ddd') + ' ' +cast(DATEPART ( Day , @rondedatum ) as varchar(2)) + ' ' + Format(@rondedatum,'MMMM') + cast(year(@rondedatum) as varchar(4)) as DatumExtraLang
,DATENAME(dw,@rondedatum) + ' ' +cast(DATEPART ( Day , @rondedatum ) as varchar(2)) + ' ' + Format(@rondedatum,'MMMM') + ' '+cast(year(@rondedatum) as varchar(4)) as DatumVoluit
,DATEPART ( DAYOFYEAR , @rondedatum ) as DagVanJaar
,DATEPART ( WEEKDAY , @rondedatum ) as DagVanWeek
,DATEPART ( Day , @rondedatum ) as DagVanMaand
,Format(@rondedatum,'ddd') as DagNaamKort
,DATENAME(dw,@rondedatum) as DagNaamLang
,IIF(DATEPART ( WEEKDAY , @rondedatum ) >=1 AND DATEPART ( WEEKDAY , @rondedatum ) <= 5,1,0) as WerkDag
,IIF(DATEPART ( WEEKDAY , @rondedatum ) in (6,7),1,0) as Weekend
,'' as Feestdag
, 0 as Werkdagteller
,DATEPART ( WEEK , @rondedatum ) as WeekNr
,DATEPART ( ISO_WEEK , @rondedatum ) as Iso_WeekNr
,DATEPART ( MONTH , @rondedatum ) as Maand
,Format(@rondedatum,'MMM') as MaandNaamKort
,DATENAME(mm,@rondedatum) as MaandNaamLang
,DATEPART ( QUARTER , @rondedatum ) as Kwartaal
,'K' + cast(DATEPART ( QUARTER , @rondedatum ) as varchar(1)) as KwartaalNaamKort
,cast(DATEPART ( YEAR , @rondedatum ) as varchar(4)) + ' K' + cast(DATEPART ( QUARTER , @rondedatum ) as varchar(1)) as KwartaalNaamLang
,DATEPART ( YEAR , @rondedatum ) as Jaar
,DATEPART ( YEAR , @rondedatum ) *100 + DATEPART ( QUARTER , @rondedatum ) as JaarKwartaal
,cast(DATEPART ( YEAR , @rondedatum ) as varchar(4)) + ' K' + cast(DATEPART ( QUARTER , @rondedatum ) as varchar(1)) as JaarKwartaalNaam
,DATEPART ( YEAR , @rondedatum )*100+ DATEPART ( WEEK , @rondedatum ) as JaarWeek
,Cast(DATEPART ( YEAR , @rondedatum ) as varchar(4)) + ' - ' + cast(DATEPART ( WEEK , @rondedatum ) as varchar(2)) as JaarWeekNaam
,DATEPART ( YEAR , @rondedatum )*100+ DATEPART ( ISO_WEEK , @rondedatum ) as JaarIso_Week
,Cast(DATEPART ( YEAR , @rondedatum ) as varchar(4)) + ' - ' + cast(DATEPART ( iso_WEEK , @rondedatum ) as varchar(2)) as JaarIso_WeekNaam
,DATEPART ( YEAR , @rondedatum )*100+ DATEPART ( MONTH , @rondedatum ) as JaarMaand
,Cast(DATEPART ( YEAR , @rondedatum ) as varchar(4)) + ' - ' + cast(DATEPART ( MONTH , @rondedatum ) as varchar(2)) as JaarMaandNaamKort
,Format(@rondedatum,'MMM') + ' '+ Cast(DATEPART ( YEAR , @rondedatum ) as varchar(4)) as JaarMaandNaamMedium
,Format(@rondedatum,'MMMM') + ' '+ Cast(DATEPART ( YEAR , @rondedatum ) as varchar(4)) as JaarMaandNaamLang


set @rondedatum = dateadd(day,1,@rondedatum)

--select cast(@rondedatum as date)

END

select 'Loop is klaar', getdate()

select 'Kalender Updaten (Feestdagen)', getdate()

update @kalender
set 
werkdag = CASE WHEN DagVanMaand in (31) and maand = 12 THEN 0
WHEN DagVanMaand in (1) and maand = 1 THEN 0
WHEN DagVanMaand in (25,26) and maand = 12 THEN 0
WHEN DagVanMaand in (5) and maand = 5 AND Jaar < 2000 THEN 0
WHEN DagVanMaand in (5) and maand = 5 AND Jaar % 5 = 0 and jaar >= 2000 THEN 0
WHEN Jaar >= 2014 and dagvanMaand = 27 and maand = 4 THEN 0
WHEN Jaar >= 1898 and jaar <= 2013 and dagvanMaand = 30 and maand = 4 THEN 0
END,
feestdag = CASE WHEN DagVanMaand in (31) and maand = 12 THEN 'Oudjaar'
WHEN DagVanMaand in (1) and maand = 1 THEN 'Nieuwjaar'
WHEN DagVanMaand in (25,26) and maand = 12 THEN 'Kerst'
WHEN DagVanMaand in (5) and maand = 5 AND Jaar < 2000 THEN 'Bevrijdingsdag'
WHEN DagVanMaand in (5) and maand = 5 AND Jaar % 5 = 0 and jaar >= 2000 THEN 'Bevrijdingsdag'
WHEN Jaar >= 2014 and dagvanMaand = 27 and maand = 4 THEN 'Koningsdag'
WHEN Jaar >= 1898 and jaar <= 2013 and dagvanMaand = 30 and maand = 4 THEN 'Koninginnedag'
END


select 'Kalender Updaten (Werkdagen + werkdagtellers)', getdate()

--Werkdagteller runnen

declare @i as int = 0
declare @current_value int = 0
declare @werkdag as int

while @i < (select max(periodeid) from @kalender)
BEGIN

update @kalender


set werkdagteller =iif(werkdag = 0 , @current_value , @current_value + 1)
from @kalender
where PeriodeID = @i

set @werkdag = (select werkdag from @kalender where periodeid = @i)

set @current_value = iif(@werkdag = 0 , @current_value , @current_value + 1)
set @i = @i +1

set @print = cast(@i as varchar(max)) + ' van ' + cast((select max(periodeid) from @kalender) as varchar(max))
print @print

END

select 'Inserten in fysieke tabel', getdate()

IF OBJECT_ID (N'shared.dim_kalender', N'U') IS NOT NULL 
BEGIN
drop table shared.dim_kalender
END


CREATE TABLE [shared].[dim_kalender](
[PeriodeID] [int] DEFAULT 0,
[Datum] [date] NOT NULL,
[DatumMedium] [nvarchar](25) DEFAULT '',
[DatumLang] [nvarchar](25) DEFAULT '',
[DatumExtraLang] [nvarchar](25) DEFAULT '',
[DatumVoluit] [nvarchar](50) DEFAULT '',
[DagVanJaar] [int] DEFAULT 0,
[DagVanWeek] [int] DEFAULT 0,
[DagVanMaand] [int] DEFAULT 0,
[DagNaamKort] [nvarchar](4000) DEFAULT '',
[DagNaamLang] [nvarchar](30) DEFAULT '',
[WerkDag] [int] DEFAULT 0,
[Weekend] [int] DEFAULT 0,
[Feestdag] [nvarchar](25) DEFAULT '',
[Werkdagteller] [int] DEFAULT 0,
[WeekNr] [int] DEFAULT 0,
[Iso_WeekNr] [int] DEFAULT 0,
[Maand] [int] DEFAULT 0,
[MaandNaamKort] [nvarchar](30) DEFAULT '',
[MaandNaamLang] [nvarchar](4000) DEFAULT '',
[Kwartaal] [int] DEFAULT 0,
[KwartaalNaamKort] [nvarchar](2) DEFAULT '',
[KwartaalNaamLang] [nvarchar](25) DEFAULT '',
[Jaar] [int] DEFAULT 0,
[JaarKwartaal] [int] DEFAULT 0,
[JaarKwartaalNaam] [nvarchar](7) DEFAULT '',
[JaarWeek] [int] DEFAULT 0,
[JaarWeekNaam] [nvarchar](25) DEFAULT '',
[Jaar_IsoWeek] [int] DEFAULT 0,
[Jaar_IsoWeekNaam] [nvarchar](25) DEFAULT '',
[JaarMaand] [int] DEFAULT 0,
[JaarMaandNaamKort] [nvarchar](25) DEFAULT '',
[JaarMaandNaamMedium] [nvarchar](25) DEFAULT '',
[JaarMaandNaamLang] [nvarchar](25) DEFAULT ''
) ON [PRIMARY]



INSERT INTO [shared].[dim_kalender]
select * from @kalender

Answer 1

No tsql on Redshift. No easy way to create a calendar table using Redshift. Redshift does not support any way to create rows on a table e.g. generate_series() is not supported

Very easy to create a calendar table elsewhere (e.g. excel or python) and upload to redshift.

Take a look here for a sample table that AWS provides https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-create-sample-db.html

Or you can search on stackoverflow and find a number of solutions.

My preferred approach is something like this:

First, create your table, (with diststyle all) then..

insert into dwh.dim_date
select thisdate as date,
date_part(year,thisdate) as year,
date_part(mm,thisdate) as month,
to_char(thisdate, 'Mon') as month_name,
date_part(day,thisdate) as day_of_mon,
date_part(dow,thisdate) as day_of_week_num,
to_char(dat, 'thisdate') as day_of_week,
date_part(week,thisdate) as week_of_year,
date_part(doy,thisdate) as day_of_year,
decode(date_part(dow,thisdate),0,true,6,true,false) as is_weekend
from
(select
trunc(dateadd(day, ROW_NUMBER () over ()-1, '1960-01-01')) as thisdate
from YOUR_BIG_TABLE
);

where YOUR_BIG_TABLE is a table you have on redshift with a where clause to set the right number of future dates after year 1900