SQL for YTD data up to previous friday

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

Date Time

Accepted answers

paulfound

Hi @novice1

I have updated the formula tool.

If this answers your question please mark as complete.

Thanks

novice1.yxmd

All comments

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.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335