Dynamic input and In-DB join

[JGAO]

Hello, 

I try to replace a SQL procedure with Alteryx. I need to join the daily account tables and daily transaction tables from the SQL server. However, each month I will have 30 daily account tables (named as account_YYYYMMDD) and 30 daily transaction tables (named as trans_YYYYMMDD). in this case, do I need to join 90 times if I need a 3 month summary. After the join I also need to stack all the daily output together. Do I also need to union 90 times?  Is there a way to dynamically input the table names for the join? I would like to use the in database tools as well, because each table is very big, and I want to save some time to run the workflow. Is it possible to have a batch macro with the in database tools?

For your reference, from the old SQL procedure, I would first pull the table names with the code: select name from daily_raw.sys.views. Then I will use a macro to loop though all the tables with code like: %let j = 1; %do %while(&j. <= &num_days. proc sql; select * from account left join transaction). Finally I will stack up all the output tables. 

Th

[MattBSlalom]

I'd avoid a batch macro scenario since that will force each day's activity to be run 1 day at a time, not allowing your database the option to parallelize your operations.  In this scenario, I'd say your previous process may not need much changing other than translating into the Alteryx tool.  You can use the Dynamic Input In-DB tool to get a complex / dynamic SQL query into the In-DB tools.   Use a formula tool or similar to generate the full complex SQL to run including your 2 sets of 30 tables joined & the unions between them, then pass that massive query back into the DB to generate the results and perform any other needed activity.