Link to home
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!
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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
Avatar of MrDavidThorn
MrDavidThorn

ASKER

Well the Business week is Monday to Friday, so I want to avoid Saturday / Sunday
is this the case for all your weeks? or will the official holidays not be skipped also?
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Yes, I somehow missed that in the cut and paste.
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?
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