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

[Paul_L]

Hi, 

I've just tried replacing:

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

with:

WHERE so.CreationDate BETWEEN 'STARTDATE' AND 'ENDDATE 23:59:59'

and then set up a new workflow that uses a text input for STARTDATE and ENDDATE, entered the original dates above and linked this to the original workflow, now saved as a macro with control parameters.  I'm getting the following:

Error: WORKFLOW_V1 (2): Record #1: Tool #1: Error SQLExecute: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting character string to smalldatetime data type.

As I'm just trying to replace a string, why as I getting a message about converting a character string?  I was hoping that the string replacement would work ok and the workflow would then run?

Thanks,

Paul

[Paul_L]

Latest update....  based on another example in the community, I've tried entering WHERE 1=1 in my query and then replacing

1=1

with 

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

But I'm still getting the same error.  If I physically copy and replace the string in my query it runs absolutely fine.

Help!

[JessicaS]

Hi @Paul_L

Can you post your workflow so we can get a sense?

Thanks,

[JessicaS]

Have you tried using dynamic input to modify the query?

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Modifying-SQL-Query-using-the-Dynamic-Input-...

Thanks,

[Paul_L]

Hi  JessicaS,

Thanks for replying.  Yes, I've tried that, but I'm struggling to understand how the Replacement Field drop down works.  

I've named the incoming field but it's not appearing in the list of variables that can be updated.  I'm not sure if I can post an example as you won't be able to connect to my Server.  The following discussion is exactly the problem I'm trying to solve but unfortunately the examples posted were apps rather than workflows:  

https://community.alteryx.com/t5/Data-Sources/Convert-date-in-SQL-query-using-Date-and-Action-tools/...

As mentioned above I'm currently trying to update an Update Value tool & Replace a Specific String but it does not carry out the replacement.  I can only get the workflow to run if it includes dates rather than variables (e.g. '2017-05-05' rather than STARTDATE) but then I get output based on the dates I've entered in teh SQL code, not those entered through the control parameter.

Could you post an example of using a dynamic input to vary dates?  Basically, I want to generate a list of date ranges and iterate through a workflow which I'll save as a batch macro.

Many thanks,

Paul

[Paul_L]

Hi,

I've just been looking further in to using a dynamic input.  

I will have 2 inputs:

1)  A Text Input tool containing 2 columns STARTDATE and ENDDATE, a list of start dates and a list of end dates respectively

2)  An Input tool containing my SQL query to pull the data from the SQL server - which I'd like to update dynamically

If I connect the dynamic query to the Text input then how can it update the SQL code?

If I connect the dynamic query to the input tool containing the SQL code then STARTDATE and ENDDATE don't appear in the dialog box to select them as the Replacement Field?

The saga continues....  Thanks for your help

[JessicaS]

Hi @Paul_L,

Attached is an example of doing a date range query with dynamic input.  I've also attached the table I used for testing.

[Paul_L]

Thanks  JessicaS,

Sorry to be a pain, could you save a 10.6 version? - we've not upgraded yet.

Looking forward to seeing it!  

[JohnJPS]

Hi Paul - just FYI, if you open the .yxmd file in a text editor, it's just XML data... you can change the version number in the first line of the XML to 10.6, and then your Alteryx software should open it. It should only fail if her file is using something not supported by 10.6.