Re: Function to calculate weekdays left in a month

  • From: Ian Cary <ian.cary@xxxxxxxxxxxxxx>
  • To: jkstill@xxxxxxxxx
  • Date: Fri, 5 Sep 2008 11:49:15 +0100

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.



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;
select count(*) into num_days
  select tdate
  from  (select sysdate tdate from dual)
  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;

select working_days() from dual;


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


For the latest data on the economy and society consult National Statistics at


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.

Other related posts: