473,326 Members | 2,128 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,326 developers and data experts.

Function To Calculate Business Days

debasisdas
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 9186

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 and end date and time. I need to calculate the...
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 calculate it between 2 fields in a database that...
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 should be 19 solar years = 6939.6018 days Easter...
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 taking into consideration up to 11 U.S. holidays. ...
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 date. What is the best, fastest and most...
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 between 2 dates amounts to 17 days. I want to now...
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 a Service Dashboard to track my team's adherence...
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 storing all the holidays in some table and not selecting...
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 holidays that don’t occur on a weekend. If I test...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.