INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

count business days

count business days

(OP)
school superintendent wants a policy where absences are escalated after five school days if a kid does not bring an excuse. Every date function I try will only count calendar days, not in-session school days. I can do this

SELECT rownum rn, date_value
FROM (SELECT Date_Value FROM Calendar_Day cd WHERE cd.InSession != 0 and schoolid=7
AND cd.Date_Value >= '05-SEP-13' ORDER BY Date_Value)

and get a list of in-session days with row numbers. Now how to use it? Have tried

with HSRN as (SELECT rownum rn, date_value
FROM (SELECT Date_Value FROM Calendar_Day cd WHERE cd.InSession != 0 and schoolid=7
AND cd.Date_Value >= '05-SEP-13' ORDER BY Date_Value))
select max(hsrn.rn) from hsrn
where hsrn.date_value <= trunc(sysdate)

and I can get a row number for today's date, but Oracle will not let me pass back a rownumber. Can I get a list of dates where rownum <= max(hsrn.rn) ? I get an error that group functions are not allowed when I try that in other subqueries.

Thanks.

RE: count business days

RMHealth,

There are straight-forward solutions to resolve your need, but to illustrate the solutions, we would need to have a populated table to run SQL against.

So, to help us to help you, could you please post SQL code to "CREATE TABLE <your table name>..." and "INSERT INTO <your table name> VALUES (...)", along with sample results that illustrate the behaviors that you would like to see from your sample data? (You don't need many rows...just enough rows to cover the scenarios you want to see.)

Thanks,

santaMufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."

RE: count business days

Conceptually, how about calculating the days as:

(Julian of today's date - Julian of earliest absence date without a note) - count (non-school days between today's date and date of earliest absence without a note) ?

====================================
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw


RE: count business days


RMHeath,

You may wish to click on thread1662-1684621: sql query - working days between two dates (including public holidays), where your problem has already been resolved, including for user-defined holidays. Let us know if this resolves your need. (Keep in mind that the holidays in the post are for 2012. You'll want to update for 2013/2014 holidays if you choose to use that solution.)

santaMufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."

RE: count business days

(OP)
John, I starred your post. Nice idea, thanks.
Santa, I'd like to build the procedures in the post you pointed to, but I am working with a proprietary interface that only allows writing to the DB through their GUI. We can extract via ODBC, nothing else. Very frustrating. Can't tell you how many times I've wanted to get away from here and into a job that lets me use Oracle. ... The grass is always greener, eh?

RE: count business days

From your original post, it appears that you have a table called calendar_day, that contains a row for every day in the calendar, and a flag to indicate whether it's a school day or not.

So if you just want to know how many school days there are between any two dates, you can do this:

CODE

SELECT COUNT(*)
FROM   calendar_day cd
WHERE  cd.InSession != 0
AND    cd.schoolid = 7      -- dunno what this does, but your query has it
AND    cd.date_value BETWEEN :date_start AND :date_end 

Exactly how you pass the start and end date parameters will depend on exactly how you're calling the query.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close