Dynamic Query with date range between dates

[JonTout]

I've inherited a query that gets rewritten each year that I'm attempting to recreate using the Dynamic Input Tool and there's an issue that I can't quite seem to resolve, any pointers would be greatly appreciated - thanks in advance.

I work in academia, courses are generally assigned an academic year based on the first day of term so we are in 2019 at the moment and the academic year start and end dates are:

Start Date 01-AUG-2020
End Date 31-JUL-2021

I have added these dates into the Text Input field and where I have a straightforward Start Date 01-AUG-2020
End Date 31-JUL-2021, the dynamic tool works fine.

However, this would be too easy so the additional date rules for the (current) academic year are:

Start date can be before 01-AUG-20 if the end date between 01-AUG-20 and 31-JUL-21
or Start date can be before 01-AUG-20 and End Date after 31-JUL-21.

In Oracle the code is written as:

WHERE
(mi.START_DATE Between '01-AUG-20' And '31-JUL-21'
OR mi.START_DATE < '01-AUG-20' AND mi.END_DATE BETWEEN '01-AUG-20' And '31-JUL-21'
OR mi.start_date < '01-AUG-20' AND mi.END_DATE > '31-JUL-21')

I've had to modify the query as Alteryx can't seem to translate "Between", so I've rewritten the code accordingly but what I find strange is that *some* of the criteria that I'd like to make dynamic, especially the dates immediately adjacent to the brackets, seems to be missing from the selection box.

Hope that makes sense, any pointers would be more than welcome.

Jon

[afv2688]

Hello @JonTout ,

You could also create a batch macro and edit all the parameters you want. You can modify as many parameters as you want. On this example I am only modifying the end date, but you could add more parameters and change different parts of the query. Each row of parameters will run once the query and union afer all ther results. Therefore in this example, if I added a different end date it will run twice and union all the records, once for end date 1 and a second for end date 2

Hope it is easy to understand.

I just used a text input just for demonstration purposes, but you can also modify the querys on the input tool.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[JonTout]

Thanks @afv2688, that's a bit advanced for me.

I've taken a similar approach, split the query into three - the "start_date" and "end_date" need to be on separate lines in the SQL query (for some reason) but taking each section and union the query seems to do the trick.  
It's not an ideal solution, but does the trick.  Will need to do a bit more testing though...

[danilang · Accepted solution]

Hi @JonTout 

For a non-macro approach, build your final SQL statement including the "Between" clauses in a Formula tool replacing the lower and upper bounds as required and then use the "Replace a Specific String" option in the Dynamic Input to replace the entire SQL statement.  I used a dummy statement "Select * from TableA"  

The output of formula looks like this 

Select * from table1 mi
WHERE
(mi.START_DATE Between '01-AUG-20' And '31-JUL-21'
OR mi.START_DATE < '01-AUG-20' AND mi.END_DATE BETWEEN '01-AUG-20' And '31-JUL-21'
OR mi.start_date < '01-AUG-20' AND mi.END_DATE > '31-JUL-21')

With this technique, there's no need to modify the SQL statement to fit what the Dynamic Input considers a "Where" clause 

Dan

[JonTout]

Thanks Dan, I like that approach very much.

Cheers,

Jon