Pass two parameters that reference same SQL field in Dynamic Input tool?

[wyliel]

Trying to use Dynamic Input and Text Input to pass Start/End date parameters into my SQL statement (e.g. "WHERE Input_Dt > 'Start Date' AND Input_DT < 'End Date'") . I find that either the Start or End parameter will pass, but not both. It's almost like since both parameters apply to the same field (Input_Dt) they can't both be passed, even though they apply to two different SQL statement. For "Modify SQL Query" Alteryx lets me replace either the Start or End WHERE statements, but not both.

I could break this step up into two separate steps (first pull data based on dynamic START date, then filter results based on a dynamic END date) but that seems inefficient. 

Any tips?

[BenMoss · Accepted solution]

A couple of things to try (the first one you might well have done, the second one I don't think you have based on what you have said, and I'm quite confident that would work)...

1) Add two statements, each replacing a different date, using the 'replace a specific string' option.

2) Use a formula tool to generate the entire statement, complete with the dynamic fields... "select * from tablename where sqldatefield >'"+[startdatefield]+"' and sqldatefield < '"+[enddatefield]+"' 

Then in your dynamic input replace the entire string (you could just put SQLQUERYHERE in your template query and then pop that in the replace text box.

Ben

[echuong1 · Accepted solution]

For the dynamic input, I suggest using "replace a specific string." You can both the start and end date. You'll input the string that you have hardcoded (dates) and replace it with the new incoming data.

[MattBSlalom · Accepted solution]

I was able to add 2 "Update Where Clause" tasks to get the behavior you're looking for.  

My SQL statement has placeholder text instead of actual Date values.

SQL Clause to Update should be a drop down with 2 options (1st clause for your start date & 2nd for your end date):

Input date fields:

[wyliel]

Doh! I had previously tried the Replace String method without success.  After taking a break to walk my dog my head got cleared. Came back and I realized there was an extra blank row in my Text Input tool. That was the problem!  Once I deleted the extra row the Replace String method worked.  Yay!  Thanks for both your replies.