Include Date Range in Input Data tool

[alexg1]

I'm trying to modify an old Access query to work in Alteryx. The original query has an option to enter date criteria before it runs. I considered using some other Alteryx tools to accomplish this, but the size of the dataset is prohibitively large to run on my machine if I filter for the dates after bringing in the data. It appears to be having some trouble around the INTO and BETWEEN operators.

      SELECT Table A.date, Table A.Office, etc....INTO [Report Name]

      FROM Table A Inner Join Table B etc...

      WHERE(((Table A.date) Between [start date] And [end date]));

If anyone can help or knows of an alternative, that would be super-helpful

Thanks!

[danilang · Accepted solution]

hi @alexg1

The INTO clause is not supported in alteryx.  Your select simply returns the records into the current data frame, i.e. the output of the Input Data tool.  As for the between, the syntax that SQL Server supports is this

Select NOAA_14D_WPC_PQPF.AsOfDate, NOAA_14D_WPC_PQPF.Station, NOAA_14D_WPC_PQPF.Percentile, NOAA_14D_WPC_PQPF.Timestamp, NOAA_14D_WPC_PQPF.Hours, NOAA_14D_WPC_PQPF.Value 
From NOAA_14D_WPC_PQPF 
Where NOAA_14D_WPC_PQPF.AsOfDate Between '2018-09-14' And '2018-09-15'

No INTO clause

Also notice the single quotes around the dates.  From what I've been able to find online, Access may require a "#" instead of the single quote, i.e. 

Between #2018-09-14# And #2018-09-15#

Hope this helps

Dan

[alexg1]

Dan,

Removing the INTO clause fixed that error. I got a few more after that, but they were all formatting related, so I managed to muddle through them.

Thanks for you help!