calculate business days (sql, oracle)

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.
teaoneAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
teaoneAuthor Commented:
thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.