SQL for YTD data up to previous friday

[novice1]

hi,

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

[novice1]

also, i need to replicate this when pulling back data for Last year

[paulfound]

Hi @novice1 

Seems to be two questions: 

1. How to pass a date dynamically to SQL.

2. How to calculate last Fridays date.

Here Is my thoughts:

Part 1.

Using a dynamic SQL don't use between in your sql use:

date >= '2020-01-01' and date <= '2020-01-02'

Then using the dynamic input tool you can pass dates to replace each one, if needed.

Part 2.

In a formula tool, paste:

datetimeadd(DateTimeNow(),(tonumber(datetimeformat(DateTimeNow(),"%w"))+2)*-1,"days")

This will give you the date last Friday, pass through the dynamic input tool.

Should all work fine.

[novice1]

Thank you. 

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

Hope this makes sense.

[paulfound]

Hi @novice1 

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.

Once scheduled it will not need any interruption. 

[novice1]

Thank you. 

how can i amend this to pull back data for last year, for the same criteria as this year?

 my SQL at the moment is a follows

GREGORIAN_DATE Between Date '2019-01-01' And (Current_Date At 'GMT') - 365

[paulfound · Accepted solution]

Hi @novice1 

I have updated the formula tool.

If this answers your question please mark as complete.

Thanks

[novice1]

thank you