Avatar of MrDavidThorn
MrDavidThorn
 asked on

Oracle syntax for Last four business workings days

Hi Experts

Can anyone help with the PL/SQL syntax to show dates that are within the last four business days on a where clause.

Many thanks!
Oracle DatabaseSQL

Avatar of undefined
Last Comment
MikeOM_DBA

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

as business days would vary depending on the business, country etc, the only real solution is to use a calendar table which, for each date, indicates if it is a business day (for you) or not.
starting from there (https://www.experts-exchange.com/Database/MS-SQL-Server/A_12267-Date-Fun-Part-One-Build-your-own-SQL-calendar-table-to-perform-complex-date-expressions.html), you would query like this:
with data as ( select PKDate , row_number() over ( order by PKDate  desc) rn from days  where PKDate  < trunc(sysdate) and is_workday = 1 ) select * from data where rn <= 4 

Open in new window

this is it about what you need to do, in short
MrDavidThorn

ASKER
Well the Business week is Monday to Friday, so I want to avoid Saturday / Sunday
Guy Hengel [angelIII / a3]

is this the case for all your weeks? or will the official holidays not be skipped also?
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
johnsone

Yes, I somehow missed that in the cut and paste.
awking00

Just to be clear, if today were Tuesday, you want a where clause that would select records with a date of yesterday, last Friday, Thursday, or Wednesday?
MikeOM_DBA

Or this (just subtract the holidays):
(  GREATEST ( NEXT_DAY ( v_start_date, 'MON') - v_start_date - 2, 0)
          + ((NEXT_DAY ( v_end_date, 'MON') - NEXT_DAY ( v_start_date, 'MON'))  / 7)  * 5)
          -  (GREATEST ( NEXT_DAY ( v_end_date, 'MON') - v_end_date - 3, 0))
          - Holidays

Open in new window

:p
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.