Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

SQL for YTD data up to previous friday

novice1
8 - Asteroid

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

7 REPLIES 7
novice1
8 - Asteroid

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

 

 

paulfound
11 - Bolide

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:

PaulFound_0-1601480794747.png

 

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.

PaulFound_1-1601480838915.png

 

Part 2.

 

In a formula tool, paste:

PaulFound_2-1601480878601.png

 

 

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
8 - Asteroid

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
11 - Bolide

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
8 - Asteroid

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
11 - Bolide

Hi @novice1 

 

I have updated the formula tool.

PaulFound_0-1601548199216.png

 

If this answers your question please mark as complete.

 

 

 

Thanks

novice1
8 - Asteroid

thank you

 

Labels