Re: help :reposting of my previous mail

Joe Celko has a solution to a version of this problem in his book "SQL for Smarties" (an excellent resource by the way). In his case the problem is to find the settlement date (5 working days after the trade  date) given a trade
date. He ends up constructing a table that essentially precomputes a settlement date for each trade date taking note of holidays and weekends. This way the computation is done once and you avoid procedural code in each transaction.

In your case, if you know the start and end dates, you could build a calendar table (workdaytable) that had a column (workday) for each  day (day) that was either one for a workday and zero if weekend or holiday.

Then selecting all the days between your start and end dates and taking the SUM of this column will give you an answer.

Something like this should work.

SELECT sum(workday)
FROM workdaytable
WHERE day >= startdate

                and day < enddate

(Whether you use >= or > depends upon YOUR definition of between.)

Hope this helps.

> Date: Mon, 19 Feb 1996 10:40:14 -0500
> Reply-to: "ORACLE database mailing list." <ORACLE-L_at_ccvm.sunysb.edu>
> From: CHANDU <KOLLAD_at_ORA66.HQ.ORA.FDA.GOV>
> Subject: help :reposting of my previous mail
> X-To: oracle-l_at_ccvm.sunysb.edu
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_ccvm.sunysb.edu>

> Hi all,
>
> Sorry for reposting this mail once again.
> I have a problem that is to compute the no of working days between 2
> dates excluding the weekends and holidays.Holidays part of the problem is
> important. I remember seeing something similar to this long back on the list.
> I would appreciate any info regarding this problem
> thanks in advance,
> chandu
> email
 kollad_at_ora66.hq.ora.fda.gov
>

Mark Saltzman, Assistant Director
Information Systems, University of Wisconsin-Extension 432 North Lake Street, Madison, Wisconsin 53706-1498 TEL: 608.263.3084 / FAX: 608.262.2343 Received on Mon Feb 19 1996 - 14:12:28 CST