This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have an SQL that is pulling data for YTD from 1st Jan 2020 up to yesterday. How can modify this, so that it would pull same data except only up to previous friday when i run this workflow on any day of the current week?
Here is extract from SQL in question
And c.GREGORIAN_DATE Between Date '2020-01-01' And (Current_Date At 'GMT') - 1 And i.FINELINE_NBR In (0105, 0205, 0910) Group By 1, 2 Order By 1, 2
the issue is - my Alteryx workflow is automated to run each Monday and every time i change it, i have to ask another team to schedule automated run for it. So i was looking for the way to select date in SQL without having to change dates in the dynamic tool.
Current set up works fine but this updates each day gives me data for up to and including yesterday.
so report that runs on Monday for this set up will always give me data up to and including yesterday
If I'm understanding correctly you have a piece SQL that has a to and from date.
SELECT ReportDate FROM Between "2020-01-01" AND "2020-09-25"
Change it to this for the purposes of updating the dates.
SELECT ReportDate FROM ReportDate >= "2020-01-01" AND ReportDate <= "2020-09-25"
Now you want to pass a fromData and to toDate to the SQL but automatically.
So what I have explained will then work any day of the week, any week of the year to pull back start of the year to last week without needing to update the workflow. You could also automate the first date of the year.
I have attached a workflow that manages the dates. Pass them through to the SQL using a dynamic tool then it will always look at 1 jan to last friday. when ever it is ran.