Tip #1: SQL zur Bestimmung Arbeitstage zwischen zwei Terminen
Werbung
Tip #1: SQL zur Bestimmung Arbeitstage zwischen zwei Terminen (Type: SQL)
Häufig kommt es vor, dass man die Arbeitstage zwischen Terminen braucht. Hier eine
Methode zur Berechnung(ausgenommen Feiertage)
Das SQL Skript zeigt einen Algirithmus der die Standard “Oracle date functions” verwendet,
um die Arbeitstage zu berechnen.
Die Methode ist nicht geeignet, Feiertage auszuschliessen. Sicher gibt es auch andere
Algorithmen, aber es soll ja nur eine Anregung darstellen.
/**************************************************/
/* Ein Beispiel zur Feststellung der Arbeitstage in SQL
*/
/*
*/
/* Der Algorithmus:
*/
/*
*/
/* 1) absolute Differenz zwischen den Terminen
*/
/*
to_date('&todate') - to_date('&frdate')
*/
/* 2) Abziehen der Wochenenden (Anzahl Wochen in Reihe */
/*
TRUNC(to_date('&todate'),'D') = 1st Tag der Woche, */
/*
in der die End-Periode liegt
*/
/*
TRUNC(to_date('&frdate'),'D') = Letzter Tag d. Woche,*/
/*
in der die Start-Periode liegt
*/
/*
Die Subtraktion ergibt die Anzahl Tage
*/
/*
zwischen den beiden Terminen ausser den tagen in
*/
/*
den Start- und Ende-Wochen. Wird diese zahl durch */
/*
7 dividiert so erhaelt man die Anzahl Wochen
*/
/*
Multipliziert mit 2 ergibt die Anzahl Wochenendtage */
/* 3) Minus 1 tag, wenn der letzte Tag ein Samstag ist
*/
/*
DECODE(to_char(to_date('&todate'),'D'),7,-1,0)
*/
/*
--> If the day of the week is saturday (7), returns -1 */
/* 4) Minus 1 tag, wenn der Starttag ein Sonntag ist
*/
/*
DECODE(to_char(to_date('&frdate'),'D'),1,-1)
*/
/*
--> Ist der Wichentag ein Sonntag (1), returns 1
*/
/* 5) Plus einem Tag, um den entspr. Tag mitzuzählen ('1 + ' )*/
/**************************************************/
define frdate = '&1'
define todate = '&2'
set verify off
select
'&frdate' From_Date
,'&todate' To_Date,
1+
to_date('&todate') - to_date('&frdate') (
(TRUNC(to_date('&todate'),'D') - TRUNC(to_date('&frdate'),'D'))/7)*2
+
DECODE(to_char(to_date('&todate'),'D'),7,-1,0)
+
DECODE(to_char(to_date('&frdate'),'D'),1,-1,0) Business_Days
from
dual
/
Das Beispiel-Skript:
SQL> @busydays 01-AUG-96 15-AUG-96
FROM_DATE TO_DATE BUSINESS_DAYS
------------------...------------- -----------------------01-AUG-96
15-AUG-96
11
1 row selected.
Derselbe Algorithmus kann auch als “stored function” abgebildet werden:
CREATE OR REPLACE FUNCTION business_days(
p_from_date IN DATE,
p_to_date IN DATE)
RETURN NUMBER IS busdays NUMBER;
BEGIN
/***********************************************************
*/
/* BUSINESS_DAYS - DB Function zum Errechnen der Ar*/
/*
beitstage zwischen zwei Terminen
*/
/***********************************************************
*/
busdays := TRUNC(p_to_date) - TRUNC(p_from_date)
- ((TRUNC(p_to_date,'D')-TRUNC(to_date(p_from_date),'D'))/7)*2
+ 1;
/* Anpassen End-Datum Samstag */
IF TO_CHAR(p_to_date,'D') = '7' THEN busdays := busdays - 1;
END IF;
/* Anpassen Start-Datum=Sonntag */
IF
TO_CHAR(p_from_date,'D') = '1' THEN busdays := busdays - 1;
END IF;
RETURN(busdays);
END;
/
show errors;
Die “database function” lautet:
SQL> select business_days('01-AUG-96','15-AUG-96') from dual;
BUSINESS_DAYS('01-AUG-96','15-AUG-96')
---------------------------------------------------------11
1 row selected.
In der “stored function” kann man auch die Feiertage berechnen, z.B:
SELECT COUNT(*) INTO nHolidays
FROM Holiday_Table
WHERE holiday_date BETWEEN p_from_date AND p_to_date;
Nun noch n Holidays von der variablen “business_days” subtrahieren und dann erst
RETURN.....
