Re: Function to calculate weekdays left in a month

This should do the trick although you need to be on 10g (maybe even 10.2 if
I recall a couple model bugs) for sql model to work.

Cheers,

Ian

create table holidays(hdate date);

Table created.

 insert into holidays values ('25-aug-08');

create or replace function working_days(sdate date default sysdate,
                                        edate date default null)
return number as
num_days number;
begin
select count(*) into num_days
  from
  (
  select tdate
  from  (select sysdate tdate from dual)
  model
  return updated rows
  dimension by (tdate)
  measures (1 as dummy)
  rules  (dummy[for tdate from trunc(sdate)
                          to nvl(edate,last_day(trunc(sdate)))
             increment numtodsinterval(1,'day')] =  1 )
  )
  where to_char(tdate,'dy') not in ('sat','sun')
  and tdate not in (select hdate from holidays);
  return num_days;
end;
/

select working_days() from dual;

WORKING_DAYS()
--------------
            18

select working_days('10-aug-08','10-sep-08') from dual;

WORKING_DAYS('10-AUG-08','10-SEP-08')
-------------------------------------
                                   22



For the latest data on the economy and society consult National Statistics at 
http://www.statistics.gov.uk

*********************************************************************************


Please Note:  Incoming and outgoing email messages are routinely monitored for 
compliance with our policy on the use of electronic communications
*********************************************************************************


Legal Disclaimer  :  Any views expressed by the sender of this message are not 
necessarily those of the Office for National Statistics
*********************************************************************************


The original of this email was scanned for viruses by the Government Secure 
Intranet virus scanning service supplied by Cable&Wireless in partnership with 
MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this 
email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or 
recorded for legal purposes.
--
http://www.freelists.org/webpage/oracle-l


Other related posts: