<\/p>\n
Wer Verträge oder sonstige Objekte in einer Datenbank speichert, möchte gegebenenfalls auch einmal die möglichen Kündigungstermine ermitteln. Und das ist gar nicht so einfach: Schon das Erfassen der relevanten Vertragsdaten hat es in sich, aber das Programmieren einer Funktion zum Ermitteln des nächstmöglichen Kündigungstermins ist eine echte Herausforderung.<\/b><\/p>\n<\/p><\/div>\n Der Anlass für diesen Beitrag ist eigentlich die ebenfalls in dieser Ausgabe vorgestellte Vertragsverwaltung, aber das Ermitteln von Kündigungsterminen ist durchaus auch für andere Anwendungen interessant – und das nicht nur für Unternehmen, sondern auch oder gerade für Privatpersonen.<\/p>\n Oder haben Sie sich noch nie geärgert, wenn Sie wieder einmal den Kündigungstermin für die Mitgliedschaft im Fitness-Studio oder für das Abo der nicht mehr benötigten Fernsehzeitung verpasst haben<\/p>\n Vertragseigenschaften<\/b><\/p>\n Das Berechnen einer Kündigungsfrist erfordert zunächst das Vorhandensein folgender Eigenschaften:<\/p>\n Verträge auf Eigenschaften abbilden<\/b><\/p>\n Mit diesen fünf Eigenschaften lassen sich alle herkömmlichen Varianten von Vertragslaufzeit\/Kündigung beschreiben. Beim Vertragsbeginn gibt es keinen Spielraum: Der ist definitiv für jeden Vertrag vorhanden. <\/p>\n Die Laufzeit wird üblicherweise in Monaten angegeben. In manchen Fällen enthält der Vertrag auch ein konkretes Vertragsende. Beides lässt sich mit der Laufzeit und dem möglichen Vertragsende beschreiben: Ein Vertrag, der vom 15.1.2006 an genau ein Jahr laufen soll, endet am 15.1.2007; die Laufzeit beträgt entsprechend 12 Monate und das mögliche Vertragsende hängt vom Vertragsbeginn ab.<\/p>\n Autoversicherungen enden am 31.12. eines Jahres, also sähen die passenden Vertragseigenschaften so aus: Der Vertragsbeginn ist irrelevant, die Laufzeit auch, aber als mögliches Vertragsende geben Sie das Ende des Jahres an.<\/p>\n Die Versicherung verlängert sich jeweils um ein Jahr, wenn Sie nicht einen Monat vor Ablauf der Versicherung kündigen; dementsprechend lässt sich das auch mit den oben beschriebenen Eigenschaften darstellen: Die Kündidungsfrist beträgt 1 (ein Monat) und die Laufzeitverlängerung 12 (zwölf Monate beziehungsweise ein Jahr).<\/p>\n Wenn ein Vertrag nur ein Jahr vom Vertragsbeginn an laufen soll, beträgt der Wert der Laufzeit 12 (zwölf Monate) und das mögliche Vertragsende „abhängig von Vertragsbeginn und Laufzeit“. Die Kündigungsfrist und die Laufzeitverlängerung betragen beide 0 (0 Monate).<\/p>\n Ein Vertrag, der mit zwei Monaten Kündigungsfrist zum Ersten eines Monats gekündigt werden kann, kann so beschrieben werden: Laufzeit ein Monat, mögliches Vertragsende „Monatsbeginn“, Kündigungsfrist zwei Monate, Laufzeitverlängerung ein Monat.<\/p>\n Denkbar wäre aber auch folgende Schreibweise: Der Vertrag ist nicht befristet (Laufzeit 0 Monate), mögliches Vertragsende ist der „Monatsbeginn“, die Kündigungsfrist beträgt zwei Monate und die Laufzeitverlängerung ebenfalls 0 Monate.<\/p>\n Kündigungsfristen<\/b><\/p>\n Bezüglich der Kündigungsfristen ist Folgendes zu beachten: <\/p>\n Verträge mit einer angegebenen Laufzeit und einer Laufzeitverlängerung von 0 Monaten haben keinen Kündigungszeitpunkt, da sie nicht gekündigt werden können.<\/p>\n Verträge mit unbefristeter Laufzeit (Laufzeit ist 0) und einer Laufzeitverlängerung von 0 Monaten können jeweils mit der angegebenen Kündigungsfrist zum angegebenen Kündigungszeitpunkt gekündigt werden (etwa zwei Monate zum Jahresende).<\/p>\n Verträge mit unbefristeter Laufzeit, aber einer Laufzeitverlängerung von x Monaten können jeweils mit der angegebenen Kündigungsfrist zu einem Vielfachen von x Monaten nach Vertragsbeginn gekündigt werden (Beispiel: Laufzeitverlängerung beträgt zwei Monate, Kündigung kann alle zwei Monate unter Berücksichtigung der angegebenen Kündigungsfrist gekündigt werden).<\/p>\n Funktion zum Berechnen des nächsten Kündigungszeitpunktes<\/b><\/p>\n Nun wäre dies kein Fachmagazin für Datenbankentwicklung, wenn wir diese Informationen nicht in einer Tabelle abbilden und eine Funktion vorstellen würden, die für alle Fälle den nächstmöglichen Kündigungszeitpunkt ermittelt.<\/p>\n Was das Datenmodell angeht, finden Sie alle notwendigen Informationen im Beitrag zur Musterlösung Vertragsverwaltung <\/b>[1]. Der vorliegende Beitrag kümmert sich ausschließlich um die Berechnung des Kündigungszeitpunktes – und das in Form einer passenden VBA-Funktion.<\/p>\n Die nachfolgend beschriebene Funktion verlangt nach den weiter oben erläuterten Parametern mit folgenden Bezeichnungen:<\/p>\n Listing 1: Diese Enumeration enthält die unterschiedlichen Kündigungszeitpunkte.<\/p>\n <\/p>\n Die Ermittlung des nächsten Kündigungszeitpunkts ist entsprechend der Vielfalt der möglichen Konfigurationen der Vertragslaufzeiten und Kündigungsfristen relativ kompliziert. Genau genommen ist es noch nicht einmal die Berechnung, die die größten Probleme beim Erstellen einer solchen Funktion macht, sondern das Ermitteln der möglichen Fälle und der unterschiedlichen Abläufe der Funktion.<\/p>\n Als große Hilfe für solche Fälle erweist es sich immer wieder, automatisiert zu testen. Nicht immer muss man dabei direkt zu einem Unit-Testing-Framework greifen – im Falle einer einzigen Funktion kann man die Tests auch einfach in eine Routine schreiben und die Ergebnisse im Direktfenster ausgeben. In diesem Fall sieht das etwa wie in Listing 2 aus.<\/p>\n Listing 2: Testaufrufe der Funktion zum Ermitteln von Kündigungszeitpunkten<\/p>\n Die Tests sind so aufgebaut, dass die Funktion jeweils mit einer Kombination der verschiedenen Parameter aufgerufen wird und die Routine das Ergebnis mit dem erwarteten Ergebnis vergleicht. Das Resultat dieses Vergleichstests wird im Direktfenster ausgegeben – natürlich unter Angabe einer den Test repräsentierenden Zahl.<\/p>\n Der große Vorteil hierbei ist, dass Sie Schritt für Schritt neue Anforderungen stellen und neuen Code zur Funktion hinzufügen, der diese Anforderungen erfüllen soll. Gleichzeitig testen Sie bei jedem Test und – besonders wichtig – nach jeder änderung der Funktion alle bisher aufgeführten Konfigurationen mit. Die Tests für das Erstellen der Funktion umfassen so knapp 100 Aufrufe, und damit sind sicher noch lange nicht alle möglichen Fälle (und damit potenzielle Fehlerquellen) abgedeckt.<\/p>\n Berechnung von Verträgen ohne feste Die Funktion unterscheidet grob zwischen Verträgen mit und ohne feste Laufzeit. Hat ein Vertrag keine feste Laufzeit, bedeutet dies ja, dass er jeweils zu dem angegebenen Kündigungszeitpunkt gekündigt werden kann – also etwa zum Monatsende oder zum Quartalsbeginn, und zwar mit der angegebenen Kündigungsfrist. Entsprechend der Anzahl der hier möglichen Kündigungszeitpunkte gibt es also sieben verschiedene Formeln beziehungsweise Programmabschnitte, nach denen der nächstmögliche Kündigungszeitpunkt berechnet werden kann. Listing 3 enthält die Berechnungsmethoden für die Verträge ohne feste Vertragslaufzeit. Stellvertretend für die anderen sollen drei der sieben Varianten vorgestellt werden: Die erste Variante ermittelt den Kündigungszeitpunkt anhand des Startdatums des Vertrags. Bei einem unbefristeten Vertrag gib es prinzipiell kein Startdatum, von dem man das Vertragsende und damit den nächstmöglichen Kündigungszeitpunkt abhängig machen könnte.<\/p>\n Daher ist der aktuelle Tag der nächste erreichbare Kündigungszeitpunkt, und das Vertragsende erfolgt um einen der Kündigungsfrist entsprechenden Zeitraum nach hinten verschoben:<\/p>\n Die anderen sechs Varianten mit vorgegebenem Zeitpunkt für das Vertragsende sind ein wenig komplizierter und nehmen einige Hilfsfunktionen in Anspruch [2]. Die Berechnung des Kündigungszeitraums für Verträge, die eine unbefristete Vertragslaufzeit haben und mit einer bestimmten Kündigungsfrist zum Monatsende kündbar sind, erfolgt so:<\/p>\n Grundlage ist wiederum das aktuelle Datum, das der Funktion per dateAktuellesDatum <\/b>übermittelt wird. Zu diesem Datum müssen Sie nur noch den letzten Tag des Monats ermitteln – fertig! Die Kündigungsfrist spielt in diesem Fall keine Rolle.<\/p>\n Betrachten wir noch die Variante der Kündigung mit einer bestimmten Kündigungsfrist bis zum Jahresende:<\/p>\n Dieser Ausdruck addiert zunächst die Kündigungsfrist zum aktuellen Datum hinzu, ermittelt das Jahresende für dieses Datum, zieht davon die Anzahl der Monate der Kündigungsfrist ab und ermittelt den letzten Tag des resultierenden Monats.<\/p>\n Berechnung von Kündigungsterminen für befristete Verträge<\/b><\/p>\n Richtig interessant wird es bei den befristeten Verträgen mit Laufzeitverlängerungen. Der nächste Kündigungstermin kann hier nicht mehr mit einem Einzeiler berechnet werden, da der Vertragsbeginn ja unter Umständen schon weit in der Vergangenheit liegt und zahlreiche Vertragsverlängerungen erfolgt sind (s. Listing 3).<\/p>\n Listing 3: Die Funktion zum Ermitteln des nächsten Kündigungszeitpunktes<\/p>\n Schauen wir uns einfach ein Beispiel an. Die einführende Case<\/b>-Anweisung legt fest, um welche Berechnung es sich handelt – hier geht es um einen Vertrag, der nur zum Quartalsende gekündigt werden kann:<\/p>\n Im zweiten Schritt ermittelt die Routine das Vertragsende entsprechend der Grundlaufzeit des Vertrags. Dazu addiert sie die Laufzeit in Monaten und erfragt mit der passenden Funktion das Ende des Quartals, in dem sich das Datum des Vertragsendes befindet.<\/p>\n\n\n
\n
Public Enum eKuendigungszeitpunkt\r\n eKuendigungszeitpunkt_Startdatum = 1\r\n eKuendigungszeitpunkt_Monatsende = 2\r\n eKuendigungszeitpunkt_Quartalsende = 3\r\n eKuendigungszeitpunkt_Jahresende = 4\r\n eKuendigungszeitpunkt_Monatsbeginn = 5\r\n eKuendigungszeitpunkt_Quartalsbeginn = 6\r\n eKuendigungszeitpunkt_Jahresbeginn = 7\r\nEnd Enum<\/pre>\n
Public Sub KuendigungszeitpunktErmittelnTest()\r\n Debug.Print 1, NaechstenKuendigungszeitpunktErmitteln(\"4.11.2006\", \"23.5.2005\", 24, _
eKuendigungszeitpunkt_Laufzeitende, 3, 12) = \"23.2.2007\"\r\n Debug.Print 2, NaechstenKuendigungszeitpunktErmitteln(\"4.11.2006\", \"12.6.2006\", 12, _
eKuendigungszeitpunkt_Laufzeitende, 1, 12) = \"12.5.2007\"\r\n Debug.Print 3, NaechstenKuendigungszeitpunktErmitteln(\"4.11.2006\", \"1.7.2003\", 0, _
eKuendigungszeitpunkt_Monatsende, 3, 0) = \"30.11.2006\"\r\n Debug.Print 4, NaechstenKuendigungszeitpunktErmitteln(\"4.11.2006\", \"1.1.2005\", 12, _
eKuendigungszeitpunkt_Monatsende, 1, 6) = \"31.12.2006\"\r\n \"viele weitere Tests\r\nEnd Sub<\/pre>\n
Laufzeit<\/b><\/p>\nCase eKuendigungszeitpunkt_Startdatum\r\n dateKuendigungszeitpunkt = _
dateAktuellesDatum<\/pre>\nCase eKuendigungszeitpunkt_Monatsende\r\n dateKuendigungszeitpunkt = _
MonatsendeErmitteln(dateAktuellesDatum)<\/pre>\nCase eKuendigungszeitpunkt_Jahresende\r\n dateKuendigungszeitpunkt = _
MonatsendeErmitteln(DateAdd(\"m\", _
-intKuendigungsfrist, _
JahresendeErmitteln(DateAdd(\"m\", _
intKuendigungsfrist, _
dateAktuellesDatum))))<\/pre>\nPublic Function NaechstenKuendigungszeitpunktErmitteln(dateAktuellesDatum As Date, _
dateVertragsstart As Date, intLaufzeit As Integer, lngKuendigungszeitpunkt As _
eKuendigungszeitpunkt, intKuendigungsfrist As Integer, _
intLaufzeitverlaengerung As Integer) As Date\r\n Dim dateKuendigungszeitpunkt As Date\r\n Dim dateVertragsende As Date\r\n If intLaufzeit = 0 Then\r\n Select Case lngKuendigungszeitpunkt\r\n Case eKuendigungszeitpunkt_Startdatum\r\n dateKuendigungszeitpunkt = DateAdd(\"m\", intKuendigungsfrist, dateAktuellesDatum)\r\n Case eKuendigungszeitpunkt_Monatsende\r\n dateKuendigungszeitpunkt = MonatsendeErmitteln(DateAdd(\"m\", -intKuendigungsfrist, _
MonatsendeErmitteln(DateAdd(\"m\", intKuendigungsfrist, dateAktuellesDatum))))\r\n Case eKuendigungszeitpunkt_quartalsende\r\n dateKuendigungszeitpunkt = MonatsendeErmitteln(DateAdd(\"m\", -intKuendigungsfrist, _
QuartalsendeErmitteln(DateAdd(\"m\", intKuendigungsfrist, dateAktuellesDatum))))\r\n Case eKuendigungszeitpunkt_Jahresende\r\n dateKuendigungszeitpunkt = MonatsendeErmitteln(DateAdd(\"m\", -intKuendigungsfrist, _
JahresendeErmitteln(DateAdd(\"m\", intKuendigungsfrist, dateAktuellesDatum))))\r\n Case eKuendigungszeitpunkt_Monatsbeginn\r\n dateKuendigungszeitpunkt = NaechsterErster(dateAktuellesDatum)\r\n Case eKuendigungszeitpunkt_Quartalsbeginn\r\n dateKuendigungszeitpunkt = DateAdd(\"m\", -intKuendigungsfrist, _
QuartalsbeginnErmitteln(DateAdd(\"q\", 1, DateAdd(\"m\", intKuendigungsfrist, _
dateAktuellesDatum))))\r\n Case eKuendigungszeitpunkt_Jahresbeginn\r\n dateKuendigungszeitpunkt = DateAdd(\"m\", -intKuendigungsfrist, _
JahresbeginnErmitteln(DateAdd(\"yyyy\", 1, DateAdd(\"m\", intKuendigungsfrist, _
dateAktuellesDatum))))\r\n End Select\r\n Else\r\n If intLaufzeitverlaengerung = 0 Then\r\n Else\r\n Select Case lngKuendigungszeitpunkt\r\n Case eKuendigungszeitpunkt_Laufzeitende\r\n dateVertragsende = DateAdd(\"m\", intLaufzeit, dateVertragsstart)\r\n Do While DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende) < dateAktuellesDatum\r\n dateVertragsende = DateAdd(\"m\", intLaufzeitverlaengerung, dateVertragsende)\r\n Loop\r\n dateKuendigungszeitpunkt = DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende)\r\n Case eKuendigungszeitpunkt_Monatsende\r\n dateVertragsende = MonatsendeErmitteln(DateAdd(\"m\", intLaufzeit, dateVertragsstart))\r\n Do While DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende) < dateAktuellesDatum\r\n dateVertragsende = _
MonatsendeErmitteln(DateAdd(\"m\", intLaufzeitverlaengerung, dateVertragsende))\r\n Loop\r\n dateKuendigungszeitpunkt = _
MonatsendeErmitteln(DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende))\r\n Case eKuendigungszeitpunkt_Quartalsende\r\n dateVertragsende = QuartalsendeErmitteln(DateAdd(\"m\", intLaufzeit, dateVertragsstart))\r\n Do While DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende) < dateAktuellesDatum\r\n dateVertragsende = _
QuartalsendeErmitteln(DateAdd(\"m\", intLaufzeitverlaengerung, dateVertragsende))\r\n Loop\r\n dateKuendigungszeitpunkt = DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende)\r\n Case eKuendigungszeitpunkt_Jahresende\r\n dateVertragsende = JahresendeErmitteln(DateAdd(\"m\", intLaufzeit, dateVertragsstart))\r\n Do While DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende) < dateAktuellesDatum\r\n dateVertragsende = _
JahresendeErmitteln(DateAdd(\"m\", intLaufzeitverlaengerung, dateVertragsende))\r\n Loop\r\n dateKuendigungszeitpunkt = DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende)\r\n Case eKuendigungszeitpunkt_Monatsbeginn\r\n dateVertragsende = NaechsterErster(DateAdd(\"m\", intLaufzeit, dateVertragsstart))\r\n Do While DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende) < dateAktuellesDatum\r\n dateVertragsende = DateAdd(\"m\", intLaufzeitverlaengerung, dateVertragsende)\r\n Loop\r\n dateKuendigungszeitpunkt = DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende)\r\n Case eKuendigungszeitpunkt_Quartalsbeginn\r\n dateVertragsende = _
NaechstenQuartalsbeginnErmitteln(DateAdd(\"m\", intLaufzeit, dateVertragsstart))\r\n Do While DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende) < dateAktuellesDatum\r\n dateVertragsende = DateAdd(\"m\", intLaufzeitverlaengerung, dateVertragsende)\r\n Loop\r\n dateKuendigungszeitpunkt = DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende)\r\n Case eKuendigungszeitpunkt_Jahresbeginn\r\n dateVertragsende = NaechsterErsterErster(DateAdd(\"m\", intLaufzeit, dateVertragsstart))\r\n Do While DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende) < dateAktuellesDatum\r\n dateVertragsende = _
NaechsterErsterErster(DateAdd(\"m\", intLaufzeitverlaengerung, dateVertragsende))\r\n Loop\r\n dateKuendigungszeitpunkt = DateAdd(\"m\", -intKuendigungsfrist, dateVertragsende)\r\n End Select\r\n End If\r\n End If\r\n NaechstenKuendigungszeitpunktErmitteln = dateKuendigungszeitpunkt\r\nEnd Function<\/pre>\nCase eKuendigungszeitpunkt_Quartalsende<\/pre>\n
dateVertragsende = QuartalsendeErmitteln(DateAdd(\"m\", intLaufzeit, dateVertragsstart))<\/pre>\n