How to dynamically change date in SQL Query using Input Tool

[asalb]

Hi Community!  I'm attempting to automate a report that I own that I execute on a weekly basis.  The database from which I'm pulling data is a 'To Date' table where I have to manually adjust an 'Activity End Dt' each week to pull back the most recent week's data:

WHERE "TO_DATE_AGG_DOOR_PY"."CURRENT_YEAR_ACTIVITY_END_DT" = to_date ('2025-05-17', 'YYYY-MM-DD') AND
            "TO_DATE_AGG_DOOR_PY"."CURRENT_YEAR_ACTIVITY_END_DT" = to_date ('2025-05-17', 'YYYY-MM-DD')

I'm needing help how I can utilize the Dynamic Input Tool to automatically add 7 days to the last date I manually entered in the SQL above (i.e. automatically adjust to  5/24/2025 next week, 5/31 the week after etc) so that I don't have to dip into the SQL each and every week...

Thanks in advance!!

[abacon · Accepted solution]

@asalb Would it work if you got the most recent saturday? I think adding 7 wouldn't work as the SQL you store in the dynamic input is going to be what you manually set. Meaning you would have to calculate the amount of weeks between the days run and the manually set date to dynamically update it. Instead of adding 7, a formula tool finds the most recent saturday and passes that to the workflow like the attached screenshot would do the same thing but would allow you to automate the workflow weekly. Then I would use the replace a specific string and change the date instead of using the update Where clause option.

Bacon

[OllieClarke]

Hey @asalb 

If you find the max date of your "TO_DATE_AGG_DOOR_PY"."CURRENT_YEAR_ACTIVITY_END_DT" field 
(say with an input data tool saying:

Select
MAX("TO_DATE_AGG_DOOR_PY"."CURRENT_YEAR_ACTIVITY_END_DT") as MAX_DATE
FROM "TO_DATE_AGG_DOOR_PY"

You could add 7 days to that value with a formula tool

DateTimeAdd([MAX_DATE],7,'days')

and then use that field in your dynamic replace to update the '2025-05-17' in your where clause.

Does that help?

Ollie

[asalb]

Hey Ollie!  Thanks for the feedback and apologies for the delay in getting back.  It seems like this would've been a really clean way to do this, however, it relies on the assumption that the most recent Saturday would be the MAX value.  I just looked at the underlying data table and, of course, there are activity end date values in the future...