CODEWITHSUNDEEP
sqlwhile-loopcountersnowflake-cloud-data-platform
Alexa
Alexa

Reputation: 41

Snowflake While loop to continue sql until @Counter reaches 365

I'm trying to make my code stop counting when it reaches a certain number, but I keep getting an error that I cannot have a select statement after the Declare.

DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= 365)
BEGIN
    
select Geography
, concat('Latest_Week_End ',current_date - @Counter) as WE_DATE
,concat(Department_ID,' ',Department_NM)
,Group_NM
,concat(category_ID,' ',category_nm)
,Sum(Dollar_sales)
,Sum(dollar_Sales_ya)
,Sum(unit_sales)
,sum(unit_sales_ya)
,sum(rom_dollars)
,sum(rom_dollars_ya)
,sum(rom_units)
,sum(rom_units_ya) from TEMP_TABLES.MR_OMNIDASH_IRI_SHARE
where geography like '%PORTLAND%'
and WE_Date in (current_date - @Counter)
group by 1,2,3,4,5
SET @Counter  = @Counter  + 7
END

Upvotes: 0

Views: 1701

Answers (1)

Adrian White
Adrian White

Reputation: 1804

Could you try a different approach ?

select  current_date() - seq4()*7 from table(generator(rowcount => 52))

Returns the last 52 week end dates for the last year.

enter image description here

Your line:

and WE_Date in (current_date - @Counter)

Could be replaced with

and WE_Date in (select  current_date() - seq4()*7 from table(generator(rowcount => 52)))

And all should be good?

Upvotes: 1

Related Questions