We help IT Professionals succeed at work.

Oracle syntax for  Last four  business workings days

122 Views
Last Modified: 2014-12-19
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!
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
Well the Business week is Monday to Friday, so I want to avoid Saturday / Sunday
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
is this the case for all your weeks? or will the official holidays not be skipped also?
Senior Oracle DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Yes, I somehow missed that in the cut and paste.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.