How to create a while loop equivalent in Alteryx iterating through a range of dates?

[hermanhmcheung]

Hi All,

I have a SQL table that looks like this:

Member ID	Gym Plan Type	Plan Start Date	Plan End Date
A1234	Monthly Plan	2022-07-11	2022-08-10
B0057	Annual Plan	2023-01-01	2023-12-31

I have been running the below SQL script to calculate the number of gym members with expiring subscription on each day for the past 30 days. I would like to automate this through Alteryx, but I don't know how. Well noted that Alteryx SQL editor does not allow the creation of temp table, and for my personal reason, I cannot create a stored procedure in SQL to solve this. Is there any other ways I can automate this daily report in Alteryx? Thanks!

  DECLARE @AsOfDate DATE = DATEADD(DAY, -1, GETDATE())
  DECLARE @ReportEndDate DATE =  DATEADD(DAY, -29, DATEADD(DAY, -1, GETDATE()))

  
  CREATE TABLE #temp (
	[Gym Plan Type] VARCHAR(255),
	[As of Date] DATE,
	[Expired] INT,
  )

  WHILE (@AsOfDate >= @ReportEndDate)
  BEGIN
	INSERT INTO #temp
	SELECT
	 [Gym Plan Type]
	,@AsOfDate AS [As of Date]
	,count(DISTINCT(CASE WHEN [Plan End Date] = @AsOfDate THEN [Member ID] END)) AS [Expired]
  FROM [DM_USER].[dbo].[Gym Subscription List]
  GROUP BY [Gym Plan Type]

  SET @AsOfDate = DATEADD(DAY, -1, @AsOfDate)
  END

  SELECT
	*
  FROM #temp

  DROP TABLE #temp

[apathetichell]

you're using in-db tools? this is moderately straight forward with dynamic input in-db in a batch macro. You would have:

	SELECT
	 [Gym Plan Type]
	,@AsOfDate AS [As of Date]
	,count(DISTINCT(CASE WHEN [Plan End Date] = @AsOfDate THEN [Member ID] END)) AS [Expired]
  FROM [DM_USER].[dbo].[Gym Subscription List]
  GROUP BY [Gym Plan Type]

You would have the Query as a plane text field called query. You would use a control parameter/action tool to connect in and change the value of Gym Plan Type (I assume you take the output for each gym plan type or something). You would use a formula with datetimenow() (or some variation of it) to edit the date in your text query. You then feed the query and the name of your connection into Dynamic Input In-DB.

This all occurs in your batch macro.

Note - I'd use something other than [] in my dummy query to denote my variables. Using a specific plan type/membership type and a specific day would allow for easier testing - and the action tool/formula tool can update those just as easily.

[hermanhmcheung]

Thanks for your prompt reply!

I followed your instructions to feed the query, date variable, and connection name into the Dynamic Input In-DB tool. However, I still don't understand how I can achieve the "looping" part and get the same result for the not only today, but also for the past 30 days.

[apathetichell]

you are looking for every day in thepast 30 to have a 30 day export? You do this outside the macro and pass those dates in via batch macro... ie:

gymplan 1... day 1.... gymplan 1... day 30.

so you use a second action tool/second control parameter to update that.

[Christina_H]

Unless I'm misunderstanding, there's no need for macros here.  Use a generate rows tool to get the dates you're interested in, use a summarize tool to count the distinct contracts ending each day, and join them together.

[apathetichell]

@Christina_H- For the outer data compilation (dates/gym plans) - yes - 100% but the macro is needed because once you get the data you have to feed it into a SQL query. Dynamic Input In-DB excepts one row (query/connection) so you need to iterate through your data via batch macro to feed in your queries.