Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Calculation of Business days, Using Brio software - Oracle DB
webwiz0822
post Nov 7 2006, 04:34 PM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 7-November 06
Member No.: 5,536



I'm designing a query using Brio Business Intelligence software, and I'm attempting to formulate some type of function to calculate business days between two dates (excluding Saturdays and Sundays, don't care about holidays). The database which is being worked with is Oracle and I've found many different Oracle functions that do this, but everytime I plug it into Brio, it never works! Always get some error message (ORACLE 936: missing expression is the most common). Even the below function doesn't work. Can anyone tell me what I'm doing wrong? Thanks in advance.

Create PROCEDURE Busdays @from_dt datetime,@to_dt datetime ,@wkends int output
AS
DECLARE
@days int,
@dp int ,
@cnt int
set @wkends=0
set @cnt=0
set @from_dt= Request_Date
set @to_dt= Completion_Date
set @days=datediff(d,@from_dt,@to_dt)
while @cnt <=@days
begin
set @dp=datepart(dw,dateadd(day,@cnt,@from_dt))
if @dp=1 or @dp=7
set @wkends=@wkends+1
set @cnt=@cnt+1
End
Go to the top of the page
 
+Quote Post
dave
post Nov 7 2006, 04:40 PM
Post #2


Advanced Member
***

Group: Members
Posts: 5,206
Joined: 8-October 04
Member No.: 785



none of that is oracle syntax

not even close im afraid
Go to the top of the page
 
+Quote Post
campbell_b
post Nov 7 2006, 05:12 PM
Post #3


Advanced Member
***

Group: Members
Posts: 91
Joined: 3-May 06
Member No.: 4,131



That looks like T-SQL code i.e. For SQL Server or Sybase perhaps .. not Oracle
Go to the top of the page
 
+Quote Post
webwiz0822
post Nov 7 2006, 05:23 PM
Post #4


Newbie
*

Group: Members
Posts: 2
Joined: 7-November 06
Member No.: 5,536



Yea, I know. Mistake. Still looking though.
Go to the top of the page
 
+Quote Post
brajm
post Nov 7 2006, 09:58 PM
Post #5


Advanced Member
***

Group: Members
Posts: 103
Joined: 21-June 05
From: Hong Kong
Member No.: 2,340



Hi!

Your requirement is to find out business days between two given dates. You can
achieve this using simple select statement. you just want to eliminate Saturday and sunday.

There is two ways to do it depending upon the version of your oracle database

Method A :

If you are using oracle 9i and later than it is straight forward to do it. Use following select statment.

WITH date_tab AS
(SELECT TO_DATE ('&from_date', 'dd-MON-yyyy')
+ LEVEL
- 1 business_date
FROM DUAL
CONNECT BY LEVEL <=
TO_DATE ('&to_date', 'dd-MON-yyyy')
- TO_DATE ('&from_date', 'dd-MON-yyyy')
+ 1)
SELECT business_date
FROM date_tab
WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN');


CODE
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> WITH date_tab AS
  2       (SELECT       TO_DATE ('&from_date', 'dd-MON-yyyy')
  3                   + LEVEL
  4                   - 1 business_date
  5              FROM DUAL
  6        CONNECT BY LEVEL <=
  7                        TO_DATE ('&to_date', 'dd-MON-yyyy')
  8                      - TO_DATE ('&from_date', 'dd-MON-yyyy')
  9                      + 1)
10  SELECT business_date
11    FROM date_tab
12   WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN');
Enter value for from_date: 10-NOV-2006
old   2:      (SELECT       TO_DATE ('&from_date', 'dd-MON-yyyy')
new   2:      (SELECT       TO_DATE ('10-NOV-2006', 'dd-MON-yyyy')
Enter value for to_date: 20-NOV-2006
old   7:                       TO_DATE ('&to_date', 'dd-MON-yyyy')
new   7:                       TO_DATE ('20-NOV-2006', 'dd-MON-yyyy')
old   8:                     - TO_DATE ('&from_date', 'dd-MON-yyyy')
new   8:                     - TO_DATE ('10-NOV-2006', 'dd-MON-yyyy')

BUSINESS_
---------
10-NOV-06
13-NOV-06
14-NOV-06
15-NOV-06
16-NOV-06
17-NOV-06
20-NOV-06

7 rows selected.

SQL>



If you want to just get how many business days are there between given dates then use following sql statment.


WITH date_tab AS
(SELECT TO_DATE ('&from_date', 'dd-MON-yyyy')
+ LEVEL
- 1 business_date
FROM DUAL
CONNECT BY LEVEL <=
TO_DATE ('&to_date', 'dd-MON-yyyy')
- TO_DATE ('&from_date', 'dd-MON-yyyy')
+ 1)
SELECT count(business_date) business_day
FROM date_tab
WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN');

CODE
SQL> WITH date_tab AS
  2       (SELECT       TO_DATE ('&from_date', 'dd-MON-yyyy')
  3                   + LEVEL
  4                   - 1 business_date
  5              FROM DUAL
  6        CONNECT BY LEVEL <=
  7                        TO_DATE ('&to_date', 'dd-MON-yyyy')
  8                      - TO_DATE ('&from_date', 'dd-MON-yyyy')
  9                      + 1)
10  SELECT count(business_date) business_day
11    FROM date_tab
12   WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN');
Enter value for from_date: 10-NOV-2006
old   2:      (SELECT       TO_DATE ('&from_date', 'dd-MON-yyyy')
new   2:      (SELECT       TO_DATE ('10-NOV-2006', 'dd-MON-yyyy')
Enter value for to_date: 20-NOV-2006
old   7:                       TO_DATE ('&to_date', 'dd-MON-yyyy')
new   7:                       TO_DATE ('20-NOV-2006', 'dd-MON-yyyy')
old   8:                     - TO_DATE ('&from_date', 'dd-MON-yyyy')
new   8:                     - TO_DATE ('10-NOV-2006', 'dd-MON-yyyy')

BUSINESS_DAY
------------
           7


Method B:

If you are using oracle 8i and former version and you have enough rows in all_objects view then
use following query to find the business days between two given date



SELECT business_date
FROM (SELECT TO_DATE ('&from_date', 'dd-MON-yyyy')
+ ROWNUM
- 1 business_date
FROM all_objects
WHERE ROWNUM <=
TO_DATE ('&to_date', 'dd-MON-yyyy')
- TO_DATE ('&from_date', 'dd-MON-yyyy')
+ 1)
WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN');



If you want to just get how many business days are there between given dates then use following sql statment.



SELECT count(business_date) business_day
FROM (SELECT TO_DATE ('&from_date', 'dd-MON-yyyy')
+ ROWNUM
- 1 business_date
FROM all_objects
WHERE ROWNUM <=
TO_DATE ('&to_date', 'dd-MON-yyyy')
- TO_DATE ('&from_date', 'dd-MON-yyyy')
+ 1)
WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN');


--------------------
Braj Kishore Mahto
( Senior Oracle App 11i DBA )
GIT,A.S. Watsons Ltd
Hong Kong

--------------------------------------------------
Go to the top of the page
 
+Quote Post
michaelasc
post Nov 8 2006, 12:52 PM
Post #6


Advanced Member
***

Group: Members
Posts: 189
Joined: 5-May 05
From: Cookeville, Tennessee
Member No.: 2,014



Hi there
Just in case you want a function, try this. After creating it you should be able to call it within a calculation as follows:

F_BUSINESS_DAYS(start_date, end_date)

By the way, the start date must be lower than or equal to the end date. You could therefore extend the function to trap that situation if you wish.

CREATE OR REPLACE FUNCTION F_BUSINESS_DAYS
(V_START_DATE IN DATE, V_END_DATE IN DATE)
RETURN NUMBER IS DAY_COUNT NUMBER := 0;
CURR_DATE DATE;
BEGIN -- loop through and update
CURR_DATE := V_START_DATE;
WHILE CURR_DATE <= V_END_DATE
LOOP
IF TO_CHAR(CURR_DATE,'DY') NOT IN ('SAT','SUN')
THEN DAY_COUNT := DAY_COUNT + 1;
END IF;
CURR_DATE := CURR_DATE + 1;
END LOOP;
RETURN DAY_COUNT;
END F_BUSINESS_DAYS;
/

I hope this helps
Best wishes
Michael Armstrong-Smith

URL: http://learndiscoverer.com
Blog: http://learndiscoverer.blogspot.com
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd September 2014 - 11:25 PM