Selecting Previous Thursday/Preceding Friday, regardless of run date

Brian H.

I'm trying to set an analysis that defaults to run for the previous payweek (which for us is Friday to Thursday), no matter what day of the week it's run on.

So for instance if run today, Tuesday 4/16, one formula would select for the previous Thurday (4/11), and another formula would select the Friday before that (4/5). But then, if run this Friday (4/19), the dates would change to 4/18 and 4/12 respectively.

I've tried several different timestampadd formulas, to no luck. Is this possible?

Thanks for helping out someone new to this!

Alexandru Burcea-Oracle

Hi Brian,

What formula are you using now, could you please share ?

Also is it in OTBI or BI Publisher ?

Regards,

Alex.

Brian H.

Hi Alex,

OTBI.

I'm currently testing the following formulas which seem to be working, but I haven't tried them on every day yet:

TIMESTAMPADD(SQL_TSI_DAY, -1 - MOD(DAYOFWEEK(CURRENT_DATE) + 2 + 5, 7) - 7, CURRENT_DATE)

for two Fridays ago, and

TIMESTAMPADD(SQL_TSI_DAY, -1 - MOD(DAYOFWEEK(CURRENT_DATE-1) + 2, 7), CURRENT_DATE)

for last Thursday.

Thanks,

Brian

Alexandru Burcea-Oracle

Hi,

You can also try a logic similar to below one

CASE WHEN

DAYOFWEEK(CURRENT_DATE)>=5

THEN TIMESTAMPADD(SQL_TSI_DAY, 5-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE

ELSE TIMESTAMPADD(SQL_TSI_DAY, -2-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)

END

Alexandru Burcea-Oracle

Hi,

You can also try a logic similar to below one

===============================================

CASE WHEN

DAYOFWEEK(CURRENT_DATE)>=5

THEN TIMESTAMPADD(SQL_TSI_DAY, 5-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)

ELSE TIMESTAMPADD(SQL_TSI_DAY, -2-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)

END

Regards,

Alex.

Alexandru Burcea-Oracle

Hi,

You can also try a logic similar to below one

================================================

CASE WHEN

DAYOFWEEK(CURRENT_DATE)>=5

THEN TIMESTAMPADD(SQL_TSI_DAY, 5-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE

ELSE TIMESTAMPADD(SQL_TSI_DAY, -2-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)

END

Regards,

Alex.

Alexandru Burcea-Oracle

Hi,

You can also try a conditional like:

==============================

CASE WHEN
DAYOFWEEK(CURRENT_DATE)>=5
THEN TIMESTAMPADD(SQL_TSI_DAY, 5-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)
ELSE TIMESTAMPADD(SQL_TSI_DAY, -2-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)
END

================================

Regards,

Alex

Alexandru Burcea-Oracle

Yes, MOD can be used but it can become tricky ( as you see "+2+5" in a MOD7 is just a "full Cycle in MOD7"

Such things might become tricky to control over time) so you can also try a conditional as below which has 2 branches depending if CURRENT_DATE is in the reporting week (days 5,6,7) or you report for previous week ( in days 1,2,3,4)

Alexandru Burcea-Oracle

CASE WHEN
DAYOFWEEK(CURRENT_DATE)>=5
THEN TIMESTAMPADD(SQL_TSI_DAY, 5-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)
ELSE TIMESTAMPADD(SQL_TSI_DAY, -2-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)
END

Alexandru Burcea-Oracle

Hi,

You can also try a logic similar to below one

CASE WHEN

DAYOFWEEK(CURRENT_DATE)>=5

THEN TIMESTAMPADD(SQL_TSI_DAY, 5-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE

ELSE TIMESTAMPADD(SQL_TSI_DAY, -2-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)

END

Alexandru Burcea-Oracle

Hi,

You can also try a logic similar to below one

CASE WHEN

DAYOFWEEK(CURRENT_DATE)>=5

THEN TIMESTAMPADD(SQL_TSI_DAY, 5-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE

ELSE TIMESTAMPADD(SQL_TSI_DAY, -2-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)

END