Q
Problem solve Get help with specific problems with your technologies, process and projects.

Counting days in 11i report

I'm looking for an easy way to count the business days between two dates in a custom report to be run in Oracle Apps 11i. Is there a function with in Oracle or Oracle Apps that can help me with this?

There are a couple of things to consider, here. If you calculate the working days, using only Saturday and Sunday, you will be counting holidays. Many companies will create a "holiday calendar" type table as well.

This solution was actually published in Oracle's ASK TOM resource recently.

The code: 
select count(*)
from ( select rownum as rnum
       from all_objects
       where rownum <= to_date('&1') - to_date('&2')+1 )
where to_char( to_date('&2')+rnum-1, 'DY' )not in ( 'SAT', 'SUN' );

It calculates work days, exclusive of holidays.

In the example, you use a table that has as many rows as the number of days you may span. For instance, if there is one year between the dates, the table you reference must have 366 rows. Because all_objects is a large table, it's a good 'generic' candidate (as Tom says).

If you create and use a table with holiday dates in it...

create table exclude_dates ( no_work date primary key ) 
organization index;
insert into exclude_dates values ( '14-FEB-2000' );

...then you can then add the "not exists.." clause to check whether the date is a work day.

select count(*)
from ( select rownum rnum
       from all_objects
       where rownum <= to_date('&1') - to_date('&2')+1 )
where to_char( to_date('&2')+rnum-1, 'DY' )  not in ( 'SAT', 'SUN' )
and not exists 
        ( select null from exclude_dates where no_work =
          trunc(to_date('&2')+rnum-1) );

Dig Deeper on Oracle E-Business Suite

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close