Help in creating a dynamic query for the database tools

[Alayna]

Hi all, 

I'm working on a complicated workflow that takes a list of employee IDs, earnings begin dates, and earnings end dates and uses it to query from a PeopleSoft connection. I basically use the summarize function to first concatenate the IDs in one string,  feed that with the dates to a formula tool to make the query, and then the dynamic input tool to run the query like below.

This works great when every employee has the same earnings begin and end dates.... but now I need to find a workaround for when employees have different earnings periods. Is this something that a macro could help with? I'm not exactly sure how to connect the macro tool with the DB tools, but I've attached a sample workflow of what i'm doing.

[BrandonB]

Hi @Alayna 

You could accomplish this by grouping employees with the same start and end dates and then creating a query for each group. You already have the logic pretty much built, so all you need is a row for each set. These could all then be fed into the dynamic input tool. This will execute each query one after another and stack the results. 

[Alayna]

Hi @BrandonB  thanks for the reply. So I have the queries in rows but not sure how to use the dynamic input. I was looking around the forum and many recommended the stored procedures approach, but my database connection says it doesn't allow for stored procedures. Also, would the data source template just be the exact same fields as the input data coming in?

[BrandonB]

In your template, you are creating a sort of "dummy" query that returns the same output schema as your other queries you plan on running. You can then configure the Dynamic Input tool like this to replace the entire query string with each row you send into it. Every row will execute as its own unique query: