count business days 1

[rmhealth]

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.

 

[SantaMufasa]

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,

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

 

[johnherman]

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

 

[SantaMufasa]

RMHeath,

You may wish to click on thread1662-1684621, 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.)

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

 

[rmhealth]

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?

 

[ChrisHunt]

From your original post, it appears that you have a table called [tt]calendar_day[/tt], 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:

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