?
Solved

calculate business days (sql, oracle)

Posted on 2014-02-13
2
Medium Priority
?
466 Views
Last Modified: 2014-02-13
How do I restrict a query results to a certain number of business days? For example, if I need something fort he past 15 business days.
0
Comment
Question by:teaone
[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
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39856561
this will return the past 15 business days (meaning all days except Saturday and Sunday)
including today.


SELECT *
  FROM (SELECT *
          FROM (SELECT SYSDATE - LEVEL - 1 d
                  FROM DUAL
                CONNECT BY LEVEL <= 21)
         WHERE TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun')
        ORDER BY d DESC)
 WHERE ROWNUM <= 15

You could then use this to join to your other table

Alternatively you could generate a range...

SELECT min(d), max(d)
  FROM (SELECT *
          FROM (SELECT SYSDATE - LEVEL - 1 d
                  FROM DUAL
                CONNECT BY LEVEL <= 21)
         WHERE TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun')
        ORDER BY d DESC)
 WHERE ROWNUM <= 15;

and then use this where your table between those values; but then you'd also have to exlude Saturday and Sunday using a condition similar to that shown
0
 

Author Closing Comment

by:teaone
ID: 39856606
thank you!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

765 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