Interative Dynamic SQL Where Statement

[Paul_L]

Hi there, 

I'm trying to repeat a workflow for a set of date periods.  I've currently got a workflow set up with a fixed range as part of some SQL code as follows:

WHERE so.CreationDate BETWEEN '2017-05-17' AND '2017-05-23 23:59:59'

I'd like to vary the start and end dates based on a input file and iterate through the list of date ranges

E.G.  

Start Date    End Date

2017-05-03  2017-05-09

2017-05-10  2017-05-16

2017-05-17  2017-05-23

2017-05-24  2017-05-30

Any suggestions for the best approach?  I was thinking add a couple of variables in place of dates and use a couple of control parameters?

Many thanks,

Paul

[JessicaS]

Hi @Paul_L,

I changed the file over for you in the event you weren't able to based on @JohnJPS's suggestion.

Please see attached.

Thanks!

[JessicaS]

Reading back on your post it looks like you have start and end date in separate columns.  You will likely want to use a formula to create a field such as '2017-08-31' AND '2017-09-06' so that you can use the dynamic input in the way I have it set up in the example.

Of course, there are many ways to do this task in Alteryx but I think using a formula to create the range that you can insert into your SQL formula should be simple enough.  Otherwise, you could use a macro to update each date separately.

Thanks,

[Paul_L]

Thanks for the tip JohnJPS, that's very useful to know.

I'm still not clear on how that solution works JessicaS.  Should the Input Data Source Template in the dynamic input tool contain a link to a file containing my SQL code?

[JessicaS · Accepted solution]

Hi @Paul_L

If you click edit on the 'input source template' you are taken to an interface that works just like the input tool.  You can select your DB table, then use the SQL editor to write a 'template' SQL query that you will replace a section of with your input to the dynamic input containing date ranges.

You can then add a parameter for 'modify SQL query' to replace part of the text in the query specified above with your input stream containing date ranges.

Thanks,

[Paul_L]

Thank you very much, by chance I had just managed to figure it out as I found a VIMEO of someone setting up a dynamic input.

It now makes a lot of sense and I'm not feeling too bright as I should have figured it out sooner!  

Many thanks for continuing to help me whilst I was struggling,

Paul