Re: Function to calculate weekdays left in a month

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: 5 Aug 2008 10:49:20 -0000
Message-ID: <OF2AB91F28.A7DF0C78-ON8025749C.003AB34D-802574BB.003B713A@ons.gsi.gov.uk>


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
Received on Tue Aug 05 2008 - 05:49:20 CDT

Original text of this message