WHILE Loop in Input Data's SQL Editor

Question

I'm currently working on automating a process that doesn't allow me to use iterative macros or In-Database tools. The process is responsible for grabbing data for a user-defined number of calendar years (YearQuantity) based on a user-defined most recent calendar year (Year1). I envisioned this process using a Counter variable (in a SQL statement) to iterate through all of the proper Calendar years, which are included as criteria in WHERE statements.

Here's the SQL code that I thought would work in the Input Data's SQL Editor:

DECLARE @Counter INT
SET @Counter=0
WHILE (@Counter < %Question.YearQuantity%)
BEGIN
SELECT Database.Table.*
FROM Database.Table

WHERE Database.Table.YYYYMMDD LIKE '%Question.Year1%-@Counter %'

Unfortunately, this code doesn't work despite trying various methods with Pre & Post SQL statements. I would greatly appreciate if somebody could share a solution that doesn't use iterative macros or In-DB tools. Thanks!!

jheit · Answer

Could you elaborate more on this? I am unsure what you meaning by building my query using Alteryx tools and then passing its executable state into a Dynamic Input.

BrandonB · Answer

If you can't use any macros, I would try to build the query using regular alteryx tools and then pass the result into a Dynamic Input tool. I have a feeling that your issue is using the variables within the query. If you have the actual query built within the workflow and passed in its executable state in a Dynamic Input tool it should work.

jheit · Answer

I have updated my original post due to formatting problems. It now includes the WHERE statements I'm using to limit the dates by the leading 4 characters.