Problem: Write a query to calculate number of working days between any 2 given dates.
The working days here refer to the count of days excluding Saturdays and Sundays. Let us take the given 2 dates as '26-Aug-2007' and SYSDATE. (26th August is my Birthday :-D ).
The query looks like:
select count(val) Number_of_working_days from
(
select
to_number(to_char(to_date('26-aug-07')+rownum , 'D')) val,
to_char(to_date('26-aug-07')+ rownum) date1,
to_char(to_date('26-aug-07') + rownum, 'Day') day1
from
all_objects
where
to_date('26-aug-07')+rownum<= sysdate
)
where val not in (6,7)
I genuinely feel that a better query can be written for this purpose. Please provide your valuable inputs!!!
Posts
oem cpu jul 23
-
this blog is no longer maintained. go there : OEM patch |
LaurentSchneider.Com (wordpress.com)
7 months ago
5 comments:
achingupta83if there is any date table exists in the oracle as we define time dimension in datawarehousing then query might be quite simple. otherwise i think, this query would be more or less same with 1 or 2 modifications.
let me know of you find anything else.
though if we use procedure then there are other solutions as well.
Your query is good show of creativity... yes, if we have a table having date values for all the days of a year, query to find no of working days can be simpler -
create table day_year (y_day date);
-- To populate day_year
begin
for i in 0..364 loop
insert into day_year values(to_date('01-jan-07','dd-mon-yy')+i);
end loop;
end;
-- Query to find no of working days
select count(y_day) from day_year where to_char(y_day,'Dy') not in ('Sat','Sun') and y_day between '&date1' and '&date2'
@Asish: Your approach is a wonderful display of the effects that 'simplicity' can produce. However I only found 1 prob with it. It is not generic. For example , in your case it will solve the purpose for the year '2007' only. I believe Sachin is amply right to state that we can achieve the purpose more simply by using a pl/sql procedure. What do you think?
select x,to_char(x,'Day') day from
(select (dt - i) x from
(select trunc((sysdate)) dt from dual) a,
(select level i from dual connect by level between 1 and 1000) b
)
where x > to_date('&&MM-&DD-&YYYY','MM-DD-YYYY')
and trim(to_char(x,'Day')) not IN ('Saturday', 'Sunday')
--Limitation: Need to envision max number of days
@Manish : You have given an excellent query. Thanks!!!!
The limit in the part "select level i from dual connect by level between 1 and 1000" can be increased by altering the OCI Array Buffer Size in Toad settings...
Post a Comment