Murachs ASP.NET 2.0 Upgraders Guide: C# Edition
Die Grundlage dieses Buches bildet die Web-Beispielanwendung „Halloween-Superstore“, anhand dieser werden Änderungen und Neuerungen in ASP.NET 2.0 gezeigt – es versteht sich von selbst, dass es sich hier um ein upgrade handelt, also nicht alle Funktionalitäten von ASP.NET erläutert werden.
Das geometrische Mittel
oder die Frage, wie berechne ich das Produkt einer Spalte einer Tabelle und warum gibt es eigentlich keine PROD() Aggregatfunktion...
Die Vorgeschichte zu diesem Beitrag:
Wir erhalten immer wieder Anrufe von irgendwelchen Vertrieblern, die uns in den buntesten Farben ausmalen, welche tolle Investmentangebote sie haben, welche astronomische Rendite erzielbar sind und warum wir unbedingt in diese Produkte investieren sollen. Meist erledigen sich solche Anrufe recht schnell von ganz alleine, aber hin und wieder ist es ganz amüsant, mal näher hinzuhören. Neulich pries so Jemand ein Produkt an, welches "wie eine Rakete" in den letzten 6 Monaten 40% zugelegt hätte und in den letzten 5 Jahren immerhin noch eine bemerkenswerte Rendite von 8% per annum aufweisen könne. Als auf meine Frage, ob diese Performance gemäß anerkannter Performance Presentation Standards ermittelt wurde, keine zufriedenstellende Antwort zurückkam, war auch dieses Gespräch schnell beendet. Dennoch blieb irgendwie bei mir die Frage hängen, wie würde man die Performance einer Geldanlage im SQL Server ermitteln? Und, vielleicht noch wichtiger, wie würde man es richtig machen?
Ausgangsszenario:
Wir investieren Anfang 2000 100,- €. In den nachfolgenden Jahren entwickelt sich der Wert unserer Anlage gemäß folgender Übersicht
| 2000 | -50% |
| 2001 | +60% |
| 2002 | +15% |
| 2003 | +40% |
| 2004 | -25% |
Wenn man jetzt das 5-Jahres-Mittel errechnet, ergibt sich (-0,5+0,6+0,15+0,4+-0,25)/5 = 0,08 => 8,0% pro Jahr innerhalb dieser 5 Jahre. Ein lukratives Investment könnte man meinen. Und mathematisch fehlerfrei nachgewiesen. ;-)
Stimmt, nur verschleiert die Verwendung des arithmetischen Mittels bei der Angabe der Performance hier die Tatsache, daß wir Geld verloren hätten, wenn wir in diese Anlage investiert hätten. Beweis? Okay!
| Performance | Endwert in Euro | |
| Anfang 2000 haben wir 100 € investiert. | 100 | |
| Ende 2000 haben wir 50% verloren. | -50% | 50 |
| Ende 2001 haben wir 60% (auf den Betrag Ende 2000 wiedergewonnen) | +60% | 80 |
| Ende 2002 | +15% | 92 |
| Ende 2003 | +40% | 128,8 |
| Ende 2004 | -25% | 96,6 |
So, aus unserem Startkapital von 100 € sind in 5 Jahren 96,6 € geworden. Dies ist die traurige Wirklichkeit hinter der Hochglanzfassade. Und genau diese Wirklichkeit wird durch das arithmetische Mittel verschleiert. Zum Einsatz kommen muß hier das geometrische Mittel. Dieses bezogen auf unser Beispiel ergibt sich als ((0.5 * 1,6*1,15*1,4*0,75) ^ 1/5) - 1 = -0,0069...
Die Rendite über die gesamte Laufzeit daraus beträgt ~ -3,4%.
So, genug der Finanzmathematik. Wie könnte eine Lösung in T-SQL aussehen?
Hm, zunächst mal mag man sich an dieser Stelle beklagen, warum es keine PROD() Aggregatfunktion gibt. Alles wäre so einfach, wenn man, ähnlich wie SUM(), eine eingebaute Funktion hätte, welche die Werte einer Spalte einer Tabelle miteinander multipliziert. Aber nein...
Also muß man selber tätig werden.
IF OBJECT_ID('tempdb.#t') >0
DROP TABLE #t
CREATE TABLE #t
(
fiscal_year INT
, fund_return FLOAT
)
INSERT INTO #t VALUES(2000, -.5)
INSERT INTO #t VALUES(2001, 0.6)
INSERT INTO #t VALUES(2002, .15)
INSERT INTO #t VALUES(2003, 0.4)
INSERT INTO #t VALUES(2004, -.25)
ist die Ausgangsbasis. Die erste Lösung, die naheliegen könnte, ist der Einsatz eines Cursor, der durch die einzelnen Zeilen läuft und die Multiplikation vornimmt.
DECLARE @fund_return FLOAT
DECLARE @result FLOAT
SELECT @fund_return = 1, @result = 1
DECLARE slash_cursors CURSOR FOR
SELECT fund_return
FROM #t
OPEN slash_cursors
FETCH NEXT FROM slash_cursors INTO @fund_return
WHILE @@FETCH_STATUS = 0
BEGIN
SET @result = (1+@fund_return) * @result
FETCH NEXT FROM slash_cursors INTO @fund_return
END
SELECT
(POWER(@result,1.0/(SELECT COUNT(*) FROM #t))-1) * 100 AS [Etwas anderes als die propagierten 8,0%]
, 100 + ((POWER(@result,1.0/(SELECT COUNT(*) FROM #t))-1) * 100) *
(SELECT COUNT(*) FROM #t) AS [Was aus 100 € in 5 Jahren wurde]
CLOSE slash_cursors
DEALLOCATE slash_cursors
GO
Etwas anderes als die propagierten 8,0% Was aus 100 € in 5 Jahren wurde
----------------------------------------------------- -------------------------------
-0.68944126856026466 96.552793657198677
(1 row(s) affected)
So, nun haben wir erst einmal unser Ergebnis und können direkt eine Performanceoptimierung durch Eliminierung des Cursors angehen. Dazu transformieren wir die Tabelle und machen so aus 5 Zeilen und 1 Spalte 1 Zeile mit 5 Spalten.
SELECT
(POWER((x.t1*x.t2*x.t3*x.t4*x.t5), 1.0/(SELECT COUNT(fund_return) FROM #t)) - 1)
* 100 AS [Etwas anderes als die propagierten 8,0%]
, 100 + ((POWER((x.t1*x.t2*x.t3*x.t4*x.t5), 1.0/(SELECT COUNT(fund_return) FROM #t)) - 1) * 100) * 5
AS [Was aus 100 € in 5 Jahren wurde]
FROM
(SELECT
MAX(CASE fiscal_year WHEN 2000 THEN 1+fund_return ELSE NULL END) AS t1
, MAX(CASE fiscal_year WHEN 2001 THEN 1+fund_return ELSE NULL END) AS t2
, MAX(CASE fiscal_year WHEN 2002 THEN 1+fund_return ELSE NULL END) AS t3
, MAX(CASE fiscal_year WHEN 2003 THEN 1+fund_return ELSE NULL END) AS t4
, MAX(CASE fiscal_year WHEN 2004 THEN 1+fund_return ELSE NULL END) AS t5
FROM #t) x
Etwas anderes als die propagierten 8,0% Was aus 100 € in 5 Jahren wurde
----------------------------------------------------- -------------------------------
-0.68944126856026466 96.552793657198677
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
So, den Cursor sind wir los, aber wirklich zufriedenstellen kam diese Lösung auch nicht, da sie zu unflexibel und starr ist. Mit dieser Konstruktion eine Performance über 30 Jahre zu ermitteln, ergibt ein ziemlich monströses CASE. Und auch in der Handhabung des Zeitraumes ist man starr und unflexibel, da dieser hartkodiert ist. Dies kann man zwar durch den Einsatz einer Variablen anstelle der fest eingegebenen Jahreszahlen vermeiden, ist aber, im Vergleich zu nächsten Variante, nicht wirklich prickelnd. Die letzte Variante profitiert von einer Eigenschaft des geometrischen Mittels. Und zwar kann man das geometrische Mittel auch darstellen, als e potenziert mit dem Durchschnitt der logarithmierten Einzelwerte.
SELECT (EXP(AVG(LOG(1+fund_return)))-1) * 100 AS [Etwas anderes als die propagierten 8,0%]
, 100 + (100 * (EXP(AVG(LOG(1+fund_return)))-1) * COUNT(*)) AS [Was aus 100 € in 5 Jahren wurde]
FROM #t
Etwas anderes als die propagierten 8,0% Was aus 100 € in 5 Jahren wurde
----------------------------------------------------- -------------------------------
-0.68944126856026466 96.552793657198677
(1 row(s) affected)
Was noch erwähnenswert ist, ist die Tatsache, daß der natürliche Logarithmus nur für Zahlen größer 0 definiert ist. Der Versuch, 0 oder eine negative Zahl zu verwenden, resultiert in einen Domänenfehler. Warum dies so ist, steht unter anderem hier.
Für unser Beispiel würde ein Domänenfehler genau dann auftreten, wenn ein Wert -1 wäre. Dies wäre dann auch gleichbedeutend mit einem Totalverlust des eingesetzten Kapital. Ist zwar sehr unwahrscheinlich, aber eine gute Implementierung sollte dies berücksichtigen und abfangen.
Die deutsche 30/360 Zinsberechnungsmethode
In den guten alten Tagen als Computer noch kaum vorhanden und noch weniger leistungsfähig waren, entwickelte sich die deutsche Zinsberechnungsmethode. Die Hauptbesonderheit dieser Methode besteht darin, daß jeder Monat fiktiv 30 Tage hat. Egal, wie viele Tage dieser Monat tatsächlich hat. Das Jahr hatte 360 Tage, egal ob Schaltjahr oder nicht. Dies vereinfachte natürlich die Zinsberechnungen, die damals manuell vorgenommen wurden, enorm. Die Ungleichmäßigkeiten dieser Methode sind mehr oder weniger signifikant. Gerade bei kürzeren Laufzeiten von Wertpapieren wirken sie sich jedoch deutlicher aus als bei längeren.
Mit Einführung des Euros hat man sich entschlossen, auf die ISMA-Regel 251 umzustellen. Diese Regel bezeichnet man auch als act/act Zinsberechnungsmethode. Sie berücksichtigt die tatsächliche Anzahl der Tage. Sowohl im Zähler als auch im Nenner. Mit Beginn der Doppelwährungsphase wurden viele der alten Wertpapieremissionen, die noch mit 30/360 berechnet wurden, Zug um Zug auf die neue Methode umgestellt. Neuemissionen auf dem Kapitalmarkt im Euroland finden in aller Regel heute nur noch mit act/act Regelung statt. Nichtsdestotrotz ist die 30/360 Regel in vielen (ausländischen) Märkten und Marktsegmenten noch präsent. Der amerikanische Kapitalmarkt wendet auch heute noch diese Regeln an. Allerdings mit einer Abweichung zur deutschen Regelung. In den USA wird der 31. eines Tages unter bestimmten Umständen mitgezählt. Die Amerikaner behandeln auch den Februar etwas anders, aber da wir im folgenden diese amerikanische Variante komplett aussen vor lassen interessieren uns diese Besonderheiten jetzt nicht. Wir sprechen hier nur von der deutschen Variante.
Frage: Wie viele Tage liegen zwischen dem 01.11.2005 und dem 01.12.2005?
Antwort: 30
Frage: Wie viele Tage liegen zwischen dem 01.11.2005 und dem 01.01.2006?
Antwort: 60
Frage: Wie viele Tage liegen zwischen dem 28.02.2005 und dem 01.03.2005?
Antwort: 3
Bevor jetzt jemand aufschreit. Stop, wir rechnen jeden Tag fiktiv mit 30 Tagen ab. Also hat auch der Februar 30 fiktive Tage. Somit liegen der 28.02.2005, der 29.02.2005 und der 30.02.2005 gemäß 30/360 Regelung zwischen beiden Tagen und somit ist 3 die richtige Antwort.
Dies ist etwas gewöhnungsbedürftig, aber korrekt. Vielleicht noch ein abschließendes Beispiel.
Frage: Wie viele Tage liegen zwischen dem 30.12.2005 und dem 31.12.2005?
Antwort: 0
Auch hier erst einmal in Ruhe nachdenken, bevor man protestiert. Gemäß unserer Konvention hat jeder Monat 30 Tage. Bei den Monaten mit 31 Tagen wird der 31. künstlich eliminiert. Somit reduziert sich diese Frage auf die Anzahl der Tage, die zwischen dem 30.12.2005 und dem 30.12.2005 liegen. Offensichtlich lautet die richtige Antwort 0.
Genug der Beispiele. Wir suchen nun also einen Algorithmus der dies abbildet.
DECLARE @dt_Start DATETIME
DECLARE @dt_Ende DATETIME
SELECT @dt_Start = '20051101', @dt_Ende = '20051201'
SELECT
DATEDIFF(MONTH, @dt_Start, @dt_Ende) * 30
+ DAY(@dt_Ende)
- DAY(@dt_Start)
- CASE WHEN DAY(@dt_Ende) = 31 THEN 1 ELSE 0 END AS [Tage30/360]
Tage30/360
-----------
30
(1 row(s) affected)
Betrachten wir einmal etwas näher dieses Statement.
Wir multiplizieren die Anzahl der Monate zwischen Ausgangs- und Enddatum mit 30 und eliminieren damit nahezu sämtliche Probleme mit den tatsächlichen Monatstagen. Anschließend addieren wir einfach die Tage des Enddatums hinzu und ziehen die Tage des Startdatums wieder ab. Auf diese Weise eliminieren wir das Problem, wenn das Startdatum nicht auch gleich einem Monatsanfang ist. Zum Schluß müssen wir noch das Problem adressieren wenn das Enddatum auf einen 31. fällt. Dies erfolgt durch den CASE Ausdruck, der bei Bedarf nochmal einen Tag subtrahiert.
Vielleicht noch einige Anmerkungen zur generellen Methodik. Vielfach kann man die wirresten Aussagen lesen, welche Tage nun verzinst werden und welche nicht mehr mitverzinst werden. Wird direkt der 1 Tag inklusive gerechnet oder nicht? Und was ist mit dem letzten Tag? Fakt ist, daß auf keinen Fall beide Tage eingerechnet werden.
Und zumindest im Wertpapiergeschäft ist es üblich, den 1. Tag einer Zinsperiode mitzuverzinsen, während der letzte Tag nicht mitverzinst wird. Dies mag in anderen Bereichen, wie bei der Berechnung von Verzugszinsen oder KK Krediten anders sein, wer aber dies auch für den Wertpapierbereich behauptet, mag sich mal in Ruhe Wertpapierurkunden von Banken durchlesen. Diese sind selbst für juristische Laien unzweideutig. Beginnend mit der Valutierung (einschließlich) bis zum Tag der nächsten Kuponzahlung (ausschließlich) werden Zinsen gerechnet.
Eigentlich ganz einfach und auch logisch, aber immer wieder ein beliebtes Thema bei der Ermittlung von Zinsabgrenzungen im Rahmen des Jahresabschlusses. Wir hatten jahrelang Spaß mit unserer Wirtschaftsprüfungsgesellschaft. Da mit Ausnahme des Prüfungsleiters das Team fast jedes Jahr wechselte, konnte man dies den neuen Prüfern immer wieder erklären.
Warum dieser Beitrag, die deutsche Zinsberechnungsmethode spielt doch, gerade im europäischen Wertpapierhandel, kaum noch eine Rolle?
Nun, in den letzten Tagen habe ich mich entschlossen, meine T-SQL Implementierung der Yield-to-maturity (YTM) Berechnung als Beitrag zu veröffentlichen. Ein Faktor bei der YTM Bestimmung ist die anzusetzende Zinsberechnungsmethode, zu der dieser Beitrag das grundlegende Verständnis bilden soll. Die demnächst veröffentlichte YTM Variante wird allerdings etwas "abgespeckt" sein. Sie wird nur die YTM auf Basis 30/360 beinhalten. Die Implementierung der anderen Zinsberechnungsmethode, wie act/act, act/360 oder act/365 überlasse ich dem interessierten Leser.
Aus den Reaktionen auf meine ersten Beiträgen aus der Reihe "Finanzmathematik" weiß ich, daß es einige Leute gibt, die anscheinend mächtig scharf darauf sind, einen solchen Algorithmus in die Finger zu bekommen. Da ich aber keine Lust habe, sämtliche Arbeit diesen Leuten abzunehmen, werde ich nur das Skelett liefern, das Filet Mignon wird sich dann jeder selber basteln müssen. Ich halte es dennoch für einen äußerst fairen Deal. ;-)
Man sollte auch in der Lage sein, die Beispiel mit der Excel Funktion TAGE360 nachzurechnen. So sollte
=TAGE360(Startdatum;Enddatum;WAHR)
identische Ergebnisse liefern. Ich selber arbeite mit einer finanzmathematischen Formelsammlung und habe meine Ergebnisse mit den Ergebnissen der entsprechenden Formel aus dieser Bibliothek verglichen. Diese Ergebnisse stimmen überein. Und liest man sich die Referenzliste dieses Softwarelieferanten durch, rechnet fast die gesamte Finanzwelt Deutschlands mit diesem Programm. Zu diesem Who-is-who gehören die Deutsche Bundesbank, das Bundesfinanzministerium und nahezu sämtliche Großbanken. Zwar verifiziert dies nicht die Richtigkeit des Algorithmus, aber es ist zumindest tröstlich, das im Falle eines Falles alle gleichermaßen falsch rechnen würden. :-)
Warum diese besondere Betonung auf die Korrektheit der Ergebnisse? Nun, gerade in Excel Communities stößt man häufig auf inkorrekte, falsche und/oder unvollständige Aussagen zu der TAGE360 Formel.
VS 2005 & SQL 2005 auf MSDN downloadbar
Neue Microsoft Zertifizierungen
Fälligkeitstermine für Optionen ermitteln
Im Beitrag Fälligkeiten des Bund-Futures ermitteln... haben wir uns mit dem Problem auseinandergesetzt, den jeweils 10. ten Tag eines Verfallsmonats im Bund-Future zu ermitteln. Das "einzige" Problem dieser Fragestellung war, ob der ermittelte Tag ein gültiger Börsenhandelstag ist. Ein anders gelagertes Problem erwartet uns, wenn wir uns mit Optionen auseinandersetzen.
Wir handeln fast ausschließlich mit Aktienoptionen, die an der Eurex gehandelt werden. Diese Optionen haben als Verfallstag vereinfacht gesagt den jeweils 3.ten Freitag eines Monats. Wer es genau wissen will, sollte dem Link folgen, und die Kontraktspezifikationen der Eurex selber durchlesen. Die Ermittlung diese sogenannten "Hexensabbats" könnte folgendermaßen aussehen.
Zunächst erstellen wir wieder unsere kalendarische Hilfstabelle, die wieder einmal den Großzeil der Arbeit abnimmt.
CREATE TABLE Dates
(
FullDate SMALLDATETIME
CONSTRAINT pk_Dates PRIMARY KEY(FullDate)
)
INSERT INTO Dates
SELECT DATEADD(day, Number, '20050401')
FROM master..spt_values
WHERE Number BETWEEN 0 AND 256 AND Type='P'
Die entsprechende Abfrage sieht so aus:
SELECT t1.FullDate Hexensabbat
FROM Dates t1
WHERE YEAR(t1.FullDate)= 2005
AND DATENAME(dw,t1.FullDate)='Friday'
AND
(SELECT COUNT(*)
FROM Dates AS t2
WHERE YEAR(t2.FullDate) = 2005
AND DATEPART(month,t2.FullDate) = DATEPART(month,t1.FullDate)
AND DATEPART(day,t2.FullDate) <= DATEPART(day,t1.FullDate)
AND DATENAME(dw,t2.FullDate) = 'Friday'
) = 3
ORDER BY t1.FullDate
Hexensabbat
------------------------------------------------------
2005-04-15 00:00:00
2005-05-20 00:00:00
2005-06-17 00:00:00
2005-07-15 00:00:00
2005-08-19 00:00:00
2005-09-16 00:00:00
2005-10-21 00:00:00
2005-11-18 00:00:00
(8 row(s) affected)
Was passiert?
Innerhalb der WHERE Klausel werden all Daten des uns interessierenden Zeitraumes gefiltert, bei denen der Wochentagsname "Friday" und die folgende Bedingung erfüllen:
(SELECT COUNT(*)
FROM Dates AS t2
WHERE YEAR(t2.FullDate) = 2005
AND DATEPART(month,t2.FullDate) = DATEPART(month,t1.FullDate)
AND DATEPART(day,t2.FullDate) <= DATEPART(day,t1.FullDate)
AND DATENAME(dw,t2.FullDate) = 'Friday'
) = 3
Ist COUNT(*) = 3 erfüllt, ist das entsprechende Datum (im Zusammenhang mit den anderen Bedingungen) der 3.te Freitag eines Monats.
Fälligkeiten des Bund-Futures ermitteln
Zunächst einmal ein ganz, ganz wenig Hintergrundwissen. Der Bund-Futures ist ein Zinsderivat mit einer Laufzeit von 3 Monaten oder einem Vielfachen davon. Die Fälligkeit eines Kontraktes fällt in die Monate März, Juni, September und Dezember. Ein tatsächlich reger Handel findet aber nur in dem Kontrakt mit der nächsten Fälligkeit statt. Durchaus üblich ist aber auch ein sog. "Roll-over", wenn man von einem Kontrakt in einen Kontrakt mit späterer Fälligkeit wechselt. Sei es, um die Absicherungsmaßnahme zu verlängern; sei es, um zu versuchen, doch noch einen positiven Saldo aus sämtlichen Kontrakten zu verbuchen, wenn die Position gegen Einen gelaufen ist.
Angenommen, wir haben jetzt zur Absicherung unseres Festzinsportfolios eine Postition im Juni Bund-Futures eröffnet und sind nun daran interessiert, zu erfahren, wann denn die Fälligkeitstermine bis Jahresende 2005 sind. Wie kann das mit T-SQL gelöst werden?
Die wahrscheinlich einfachste Lösung, besteht in der Verwendung einer kalendarischen Hilfstabelle, die, wie bereits in anderen Beiträgen angedeutet, für eine Vielzahl von Fällen eine immense Hilfe und Erleichterung bedeutet.
CREATE TABLE Dates
(
FullDate SMALLDATETIME
CONSTRAINT pk_Dates PRIMARY KEY(FullDate)
)
INSERT INTO Dates
SELECT DATEADD(day, Number, '20050401')
FROM master..spt_values
WHERE Number BETWEEN 0 AND 256 AND Type='P'
Das obige Beispiel verwendet master..spt_values. Dies ist eine interne Hilfstabelle des SQL Servers, die diverse Systemprozeduren verwenden. Im SQL Server 2000 liegen nur die Zahlen von 0 bis 256 in fortlaufender Reihenfolge vor, weshalb wir unseren Zeitraum auf den 01.04.2005 bis irgendwann Mitte Dezember 2005 beschränken. Für längere Zeiträume oder der Erstellung solcher Tabelle im Produktionscode sollte man vielleicht besser auf eine Schleifenkonstruktion zurückgreifen. Für diese Zwecke hier, reicht es aber vollkommen aus.
So, zurück zur Ausgangslage. Da wir eine Position im Juni Kontrakt eingegangen sind, interessiert uns in erster Linie der entsprechende Tag im Juni. Zur Übersicht wollen wir aber auch die beiden anderen Fälligkeitstermine bis Jahresende 2005 wissen. Also, die September Fälligkeit und die Dezember Fälligkeit.
SET DATEFIRST 1
SELECT
CASE DATEPART(dw,t1.FullDate)
WHEN 6 THEN DATEADD(day, 2, t1.FullDate)
WHEN 7 THEN DATEADD(day, 1, t1.FullDate)
ELSE t1.FullDate
END Bund_Futures_Fälligkeit
FROM Dates t1
WHERE YEAR(t1.FullDate)= 2005
AND DAY(t1.FullDate)= 10
AND MONTH(t1.FullDate) % 3 = 0
ORDER BY t1.FullDate
Bund_Futures_Fälligkeit
------------------------------------------------------
2005-06-10 00:00:00
2005-09-12 00:00:00
2005-12-12 00:00:00
(3 row(s) affected)
Was passiert hier?
Ein Blick auf die WHERE Klausel zeigt, daß alle 10.ten Tage jeden Monats des Jahres 2005 zurückgegeben werden, bei denen MONTH(t1.FullDate) % 3 = 0 ist. Diese Bedingung erfüllen nur die Monats März, Juni, Spetember und Dezember.
Nachdem nun diese Zeilen ausgefiltert worden sind, schaut der CASE Ausdruck nach, ob das jeweilige Datum auch auf einen gültigen Wochentag fällt, sprich ein Börsenhandelstag ist.
SET DATEFIRST 1
stellt dabei sicher, daß Montag der Beginn der Woche ist und die Ermittelung nächsten Börsentages auch richtige Werte liefert. Fällt der zurückgegebene Wert auf ein Wochende, wird die entsprechende Anzahl an Tagen hinzuaddiert, um den darauffolgenden Montag zurückzugeben. Der aufmerksame Leser mag sich jetzt vielleicht fragen, was denn wäre, wenn dieser Montag ebenfalls kein Börsenhandelstag ist? Nun, in diesem Fall wäre der nächste Börsenhandelstag der Fälligkeitstermin. Also wahrscheinlich der darauffolgende Dienstag. Diese Fingerübung überlasse ich dem geneigten Leser. Für den Fall des Bund-Futures mit seinen Fälligkeitsterminen ist es nicht wahrscheinlich, daß der darauffolgende Montag ein Feiertag ist, deswegen benötige ich diesen Sicherheitscheck nicht (hoffentlich!).
Eine tadellose Sequenz
Man mag darüber denken, wie man will. Man mag sich auch streiten, ob dieses Thema in die Datenbank gehört oder nicht doch besser in die Präsentationsschicht. Ein bißchen ist das wie die Frage, was zuerst da war: Das Huhn oder das Ei. Beide Seiten haben irgendwie Recht und doch gleichzeitig wiederum auch nicht. Von daher überlasse ich es dem jeweiligen Benutzer, für welchen Ansatz er sich entscheidet.
Genug der einleitenden Worte...
Das Problem an sich kennt wahrscheinlich jeder. Man hat eine numerische Spalte in die durch gewisse Logik ein monoton steigender Wert eingegeben wird (oder werden soll). Verändern sich jedoch im Laufe der Zeit die Daten, d.h. Daten werden gelöscht und hinzugefügt, entstehen auf diese Art und Weise Löcher in der Sequenz. Wenn man diese Löcher direkt bei Eingabe stopfen will, kann man folgendes machen:
CREATE TABLE t
(
k1 INT NOT NULL
, c1 CHAR NOT NULL
CONSTRAINT pk_t PRIMARY KEY(k1)
)
GO
ist unsere Augangssituation. Wir wollen direkt beim INSERT über eine DEFAULT Einschränkung entweder die Sequenz fortführen oder aber, die Löcher stopfen, sofern vorhanden. Dazu schreiben wir folgende UDF:
CREATE FUNCTION dbo.CloseMyGaps() RETURNS INT
AS
BEGIN
RETURN
CASE
WHEN EXISTS
(SELECT *
FROM t
WHERE k1 = 1)
THEN (SELECT MIN(t1.k1) + 1
FROM t t1
LEFT OUTER JOIN t t2
ON t1.k1 = t2.k1 - 1
WHERE t2.k1 IS NULL)
ELSE 1
END
END
GO
Und definieren diese Funktion als DEFAULT für die Spalte k1 in der Tabelle.
ALTER TABLE t ADD CONSTRAINT d_k1 DEFAULT dbo.CloseMyGaps() FOR k1
GO
Jetzt können wir ein bißchen mit Eingaben spielen
INSERT INTO t(c1) VALUES('a')
INSERT INTO t(c1) VALUES('b')
INSERT INTO t(c1) VALUES('c')
INSERT INTO t(c1) VALUES('d')
SELECT *
FROM t
k1 c1
----------- ----
1 a
2 b
3 c
4 d
(4 row(s) affected)
Wie man sieht, funktionieren INSERTs tadellos. Aber schließlich haben wir auch bisher nicht die bestehende Sequenz unterbrochen. Was passiert nun bei DELETEs?
DELETE FROM t WHERE k1 IN (2,3)
INSERT INTO t(c1) VALUES('d')
INSERT INTO t(c1) VALUES('d')
DELETE FROM t WHERE k1 =1
INSERT INTO t(c1) VALUES('f')
k1 c1
----------- ----
1 f
2 d
3 d
4 d
(4 row(s) affected)
Man sieht, die Sequenz bleibt einwandfrei.
Inwieweit sich die UDF auf die Performance auswirkt, muß man im Einzelfall testen. Für ein ausgelastetes System mit vielen Datenänderungen dürfte es eher nicht geeignet sein.
