Automating a report to get results for the new month

[rajatjain1]

Hi All,

I have a report which i am looking to automate. Basically its a single SQL Query. I need to automate it so it can run on alteryx gallery and generate report once every month .

How can one update a particular sql query and change the given month to the next month after every run?

My query has condition like A.date >  2019-08-01 and A.date < 2019-09-01 

After the report is run..the next month run should be 

A.date >  2019-09-01 and A.date < 2019-10-01

[MichalM]

@rajatjain1 

I assume the date range could be inferred from the date when the query is about to get executed? If that's the case you can use DatTimeNow tool to get the date of run followed by the Formula tool to calculate your date from and date to. You can then feed these into the Dynamic Input tool and dynamically change the dates.

Here's more on Dynamic Input.

[rajatjain1]

So if the run is today it should taken in the last whole month as the date and run for it..

[MichalM · Accepted solution]

In which case something like the below should work

DateTimeAdd(DateTimeFirstOfMonth(),-1,"month")

DateTimeAdd(DateTimeLastOfMonth(),-1,"month")

[rajatjain1]

Thank you so much it worked!