Loop with variables

Database
New Member

Loop with variables

Hi All

 

Please can someone help with where i'd even start to convert my script in Teradata or have a better solution to get a table with dates

 

Appreciate the help

 

CREATE TABLE #tempDate (

eDate DATE)

DECLARE @Date6m DATE

SET @Date6m = DATEADD(m,-6,getdate())

WHILE @Date6m < GETDATE()

BEGIN

INSERT INTO #tempDate

SELECT CONVERT(DATE,@Date6m)

 

SET @Date6m = DATEADD(d,1,@Date6m)

END

SELECT *

FROM #tempDate

DROP TABLE #tempDate

1 REPLY
Senior Apprentice

Re: Loop with variables

No need for a loop, in a DWH you will always find a calendar table.

There's usually a calendar based on your company's rules, otherwise simply use sys_calendar.calendar:

SELECT calendar_date
FROM sys_calendar.calendar
WHERE calendar_date BETWEEN ADD_MONTHS(CURRENT_DATE, -6) AND CURRENT_DATE

Of course you can materialize that Select as a Volatile Table, too:

 

CREATE VOLATILE TABLE #tempDate AS
(
SELECT calendar_date
FROM sys_calendar.calendar
WHERE calendar_date BETWEEN ADD_MONTHS(CURRENT_DATE, -6) AND CURRENT_DATE
) WITH DATA 
ON COMMIT PRESERVE ROWS;