Re: Function to calculate weekdays left in a month
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-lReceived on Tue Aug 05 2008 - 05:49:20 CDT