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.
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
