Solved

Oracle syntax for  Last four  business workings days

Posted on 2014-12-19
8
89 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!
0
Comment
Question by:MrDavidThorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40508903
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 (http://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
0
 

Author Comment

by:MrDavidThorn
ID: 40508911
Well the Business week is Monday to Friday, so I want to avoid Saturday / Sunday
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40508923
is this the case for all your weeks? or will the official holidays not be skipped also?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 35

Accepted Solution

by:
johnsone earned 250 total points
ID: 40508925
Assuming that you don't want to account for holidays, and only care about Saturday/Sunday, then this should work.
SELECT d 
FROM   (SELECT d 
        FROM   (SELECT Trunc(SYSDATE) - LEVEL + 1 d 
                FROM   dual 
                CONNECT BY LEVEL <= 7) 
        WHERE  Trim(To_char(d, 'Day')) NOT IN ( 'Saturday', 'Sunday' ) 
        ORDER  BY d) 
WHERE  ROWNUM <= 4; 

Open in new window

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 40508928
ORDER  BY d)
should that not be :
ORDER  BY d desc)
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40508930
Yes, I somehow missed that in the cut and paste.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40509265
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?
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40509278
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
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MYSQL responding very slow 3 48
SQL Calculation syntax based on a parameter 2 30
sql update 2 36
SQL Select Query help 1 34
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question