472,334 Members | 1,544 Online

# Function To Calculate Business Days

8,127 Expert 4TB
This function takes 2 dates as parameter and returns the number of working days. You need to add the list of holidays. (I have added a few as sample)

Expand|Select|Wrap|Line Numbers
1. CREATE OR REPLACE FUNCTION BUSINESS_DAYS (
2.   i_Date1 IN DATE,
3.   i_Date2 IN DATE
4.   )
5. RETURN NUMBER
6. IS
7.   V_LEAST          DATE := TRUNC(LEAST(i_Date1, i_Date2));
8.   V_GREATEST       DATE := TRUNC(GREATEST(i_Date1, i_Date2));
9.   V_RESULT         NUMBER;
10.
11.   V_FIRST_DOWK     NUMBER;
12.   V_LAST_DOWK      NUMBER;
13.   V_WKS_BTWN       NUMBER;
14.   V_SAT_ADJST      NUMBER := 0;
15.   V_COUNT_FIRST    NUMBER := 1;
16.   V_PLS            PLS_INTEGER := 1;
17. BEGIN
18.
19.   V_FIRST_DOWK := TO_NUMBER( TO_CHAR(V_LEAST, 'D' ) );
20.   V_LAST_DOWK := TO_NUMBER( TO_CHAR(V_GREATEST, 'D' ) );
21.   V_WKS_BTWN := TRUNC( (V_GREATEST - V_LEAST ) / 7 );
22.
23.   IF V_FIRST_DOWK > V_LAST_DOWK
24.   THEN
25.      V_WKS_BTWN := V_WKS_BTWN + 1;
26.   END IF;
27.
28.   IF V_FIRST_DOWK = 7
29.   THEN
30.     V_SAT_ADJST := 1;
31.     V_COUNT_FIRST := 0;
32.   ELSIF V_FIRST_DOWK = 1
33.   THEN
34.     V_COUNT_FIRST := 0;
35.   ELSE
36.      NULL;
37.   END IF;
38.
39.   IF V_LAST_DOWK = 7
40.   THEN
41.      V_SAT_ADJST := V_SAT_ADJST - 1;
42.   END IF;
43.
44.   V_RESULT := ((V_WKS_BTWN * 5 ) + (V_LAST_DOWK - V_FIRST_DOWK + V_COUNT_FIRST) + V_SAT_ADJST) * V_PLS;
45.
46.   IF V_RESULT > 0
47.   THEN
48.  --FOR 2007
49.
50.  --FOR 2008
51.
52.  --FOR 2009
53.
54.  IF TO_DATE('01-01-2009','DD-MM-YYYY') BETWEEN i_Date1 AND i_Date2
55.     THEN
56.       V_RESULT := V_RESULT - 1;
57.     END IF;
58.
59.  IF TO_DATE('19-03-2009','DD-MM-YYYY') BETWEEN i_Date1 AND i_Date2
60.     THEN
61.       V_RESULT := V_RESULT - 1;
62.     END IF;
63.
64.  IF TO_DATE('15-07-2009','DD-MM-YYYY') BETWEEN i_Date1 AND i_Date2
65.     THEN
66.       V_RESULT := V_RESULT - 1;
67.     END IF;
68. ----------------------------------------------------------
69. ------------------add more dates here-----------
70. ----------------------------------------------------------
71.
72.   RETURN V_RESULT;
73. /*
74. EXCEPTION
75.   WHEN OTHERS
76.   THEN
77.     V_ERROR := TO_CHAR(SQLERRM);
78. --    INSERT INTO CHARAN(NAME, Dt)
79. --    VALUES(V_ERROR || ' - ' || i_Date1 || ' & ' || i_Date2, SYSDATE);
80. --    COMMIT;
81. --    RAISE_APPLICATION_ERROR('-20001', 'Oops. Invalid Date format. It should be "DD-MON-YYYY"');
82.     RAISE_APPLICATION_ERROR('-20001', TO_CHAR(SQLERRM) || ' - ' || i_Date1 || ' & ' || i_Date2);
83. */
84. END;
85.
86.
Jun 30 '09 #1
0 8983

Sign in to post your reply or Sign up for a free account.

### Similar topics

 12 by: Daniela Nii | last post by: Hi there, I am looking for a javascript function that someone might have already written to calculate the paid-time-off. Given are the start... 5 by: SimonC | last post by: Help needed for a Javascript beginner. As above in the subject... i need a javascript to run this, but not in the form of a web-page. I want to... 1 by: jimfortune | last post by: From: http://groups-beta.google.com/group/comp.databases.ms-access/msg/769e67e3d0f97a90?hl=en& Errata: 19 solar years = 2939.6018 days... 18 by: jimfortune | last post by: I have an A97 module called modWorkdayFunctions in: http://www.oakland.edu/~fortune/WorkdayFunctions.zip It allows the counting of workdays... 7 by: Sam | last post by: Hi, I use C# in my ASP.NET projects. Here's what I need to do: I want to add x business days to a given date i.e. add 12 business days to today's... 3 by: Libber39 | last post by: Hi everyone, Have a query on how to calculate the amount of weeks and days contained in a number in an access query. ie: the difference in days... 2 by: rmmahara | last post by: Hi Folks, I've been reading these forums for a while and now I'm in desperate need of help, so I thought I'd post! Background: I'm creating... 2 by: rahulae | last post by: help me with this I'm able to calculate total working days excluding weekends but how to exclude holidays,is there any other way apart from... 4 by: OzNet | last post by: I have some functions to calculate the working days in a given period. This includes a table that is queried to calculate the number of public... 0 by: concettolabs | last post by: In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool... 0 by: better678 | last post by: Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented... 0 by: teenabhardwaj | last post by: How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of... 0 by: CD Tom | last post by: This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right... 0 by: CD Tom | last post by: This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins... 0 by: Naresh1 | last post by: What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge... 0 by: jalbright99669 | last post by: Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made... 0 by: antdb | last post by: Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was... 0 by: AndyPSV | last post by: HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.