Tag: "entwicklung"

Vorsicht bei der Verwendung von ISNUMERIC()

Posted on Jun 1, 2005 von in SQL Server

Liest man sich die Onlinehilfe von SQL Server durch und gelangt an das Thema ISNUMERIC(), erhält man den Eindruck, daß dies eine einfache, schnelle und sichere Methode ist, um zu überprüfen, ob ein gegebener Ausdruck in einen von SQL Server unterstützten numerischen Datentypen umgewandelt werden kann. Also, in einen der Datentypen: INTEGER, FLOAT (REAL), DECIMAL (NUMERIC) und MONEY.

Ganze Geschichte »

Present und Future Value einer Einmalanlage

Posted on Mai 6, 2005 von in SQL Server

Der Spatz in der Hand ist mehr wert als die Taube auf dem Dach. So ähnlich lautet ein beliebtes Sprichwort. Übertragen auf Geld würde man wahrscheinlich sagen, daß 1 Euro in der Hand heute mehr wert ist als 1 Euro, den man irgendwann in der Zukunft erhält. Warum? Nun, den Euro, den ich heute habe, kann ich investieren, erhalte dafür z.B. Zinsen und habe so schließlich mehr Geld als diesen Euro in der Zukunft.

Den Vorgang von Present zu Future Value nennt man "Compounding". Damit ist ein arithmetischer Prozess definiert, der den Endwert eines Cash Flows oder einer Serie von Cash Flows feststellt, wenn man "Compounded Interest" unterstellt. Auf Deutsch würde man hier Zinseszinsrechnung sagen.

So, genug der Einleitung. Skizzieren wir einmal ein Beispielszenario zu unserer Fragestellung:
Angenommen, ich habe 1.000€ und kann diese zu einem Zinssatz von 6,00% investieren. Wieviel Geld habe ich nach einem Jahr?

Formelmäßig könnte man das folgendermaßen notieren:
FV=PV+PV*i
=> FV=PV*(1+i)^1
=> 1.000*(1+0,06)^1
=> 1.060

Dies ist einfach nachzuvollziehen und auch einfach in T-SQL nachzubauen:

DECLARE @pv FLOAT
DECLARE @i FLOAT
DECLARE @n FLOAT
SELECT
@pv=1000, @i=0.06, @n=1
SELECT
ROUND(@pv*POWER(1+@i, @n),2)

-----------------------------------------------------
1060.0

(1 row(s) affected)

Hier an diesem Beispiel wird bereits eine weitere "Besonderheit" deutlich. Ich verwende idR FLOAT Daten und Datentypen. Ich habe hier die Erfahrung gemacht, daß FLOAT gerade bei komplexeren Berechnungen "genauer" rechnet als z.B. DECIMAL. Zumindest ist der Grad der "Abweichung vom exakten Ergebnis" für mich und die Zwecke dieser Berechnungen akzeptabel. Ich möchte aber darauf hinweisen, daß der geneigte Leser sich sein eigenes Urteil bilden sollte und, abhängig davon, wofür er diese Berechnungen einsetzt, beide Alternativen (FLOAT und DECIMAL) anhand seiner eigenen Daten durchspielen sollte. Ich vermute, die Antwort darauf, was der "besser" geeignete Datentyp ist, lautet: "Das hängt davon ab...".

So, zurück zu unserer Einführung. Wie wir gesehen haben, werden nach einem Jahr aus 1.000€ bei 6,00% Zins 1.060€. Angenommen ich lasse diese 1.000 aber nun 5 Jahre zu diesem Zinssatz angelegt und kann gleichzeitig die erhaltenen Zinsen ebenfalls zu diesem Zinssatz investieren. Wieviel Geld habe ich nach 5 Jahren?

FV=PV*(1+i)(1+i)(1+i)(1+i)(1+i)
=> PV*(1+i)^5
=> 1.000*(1+0.06)^5
=> 1.338,23

Wenn man sich jetzt die T-SQL Implementation ansieht, erkennt man, daß man eigentlich "nur" die Variable @n auf den neuen Wert anpassen muß:

DECLARE @pv FLOAT
DECLARE @i FLOAT
DECLARE @n FLOAT
SELECT
@pv=1000, @i=0.06, @n=5
SELECT
ROUND(@pv*POWER(1+@i, @n),2)

-----------------------------------------------------
1338.23

(1 row(s) affected)

Einfach.

Bis jetzt sind wir aber davon ausgegangen, daß unser Investment einmal im Jahr Zinsen ausschüttet. Gerade aber im amerikanischen Finanzraum ist eine häufigere Ausschüttung (halb- oder sogar vierteljährlich) üblich.

Unterstellen wir, daß in unserem 5 Jahres Beispiel, die Zinsschüttung halbjährlich erfolgt. Was müssen wir jetzt berücksichtigen?
Als Erstes rechnen wir den jährlichen Zinssatz in einen "periodengerechten" um. Also 6.00% / 2 = 3,00%.
Danach rechnen wir die Laufzeit in Jahren in Laufzeit pro Zinsperiode um. Also: 5 (Jahre) * 2 (x jährlich Zinsausschüttung) = 10 Zinsperioden.

So, in einer Formel ausgedrückt, hat sich damit nichts verändert.

DECLARE @pv FLOAT
DECLARE @i FLOAT
DECLARE @n FLOAT
SELECT
@pv=1000, @i=0.03, @n=10
SELECT
ROUND(@pv*POWER(1+@i, @n),2)

-----------------------------------------------------
1343.9200000000001

(1 row(s) affected)

Jetzt kann man die Umrechnung in periodengerechte Werte vorher vornehmen. Oder, was vielleicht geschickter und generischer ist, die Formel umstellen, indem einfach ein weiterer Parameter @m eingebaut wird. @m nimmt die Anzahl der Zinszahlungen pro Jahr auf. Damit sieht unsere Formel jetzt folgendermaßen aus:

DECLARE @pv FLOAT
DECLARE @i FLOAT
DECLARE @m FLOAT
DECLARE @n FLOAT
SELECT
@pv=1000, @i=0.06, @m=2, @n=5
SELECT
ROUND(@pv*POWER(1+@i/@m,@m*@n),2)

-----------------------------------------------------
1343.9200000000001

(1 row(s) affected)

Offensichtlich wird aus 1.000€ bei halbjährlicher Zinszahlung mehr als bei jährlicher Zinszahlung. Dies ist Schulmathematik und bedarf keiner weiteren Erklärung hier. Interessant aber ist die Frage nach der Effektivverzinsung meines Investments. Diese läßt sich durch die folgende Formel ermitteln:

DECLARE @i FLOAT
DECLARE @m FLOAT
SELECT
@i=0.06, @m=2
SELECT
(POWER(1+@i/@m,@m)-1)*100

-----------------------------------------------------
6.0899999999999954

(1 row(s) affected)

Die Multiplikation *100 ist nicht zwingend notwendig. Ich habe sie hier nur eingebaut, um einen direkten Vergleich mit Excel's EFFEKTIV() Funktion zu erleichtern. Im Grunde ist dies nicht anderes als die T-SQL Adaption dieser Excel Funktion. Vorsicht! An dieser Stelle ein ROUND() einzubauen, wäre nicht sehr klug. Gerade wenn man auf die Multiplikation mit 100 verzichten will:

DECLARE @i FLOAT
DECLARE @m FLOAT
SELECT
@i=0.06, @m=2
SELECT
ROUND((POWER(1+@i/@m,@m)-1),2)

-----------------------------------------------------
5.9999999999999998E-2

(1 row(s) affected)

Was nichts anderes ist als der ursprüngliche Eingangszinssatz.

Die bisherigen Berechnungen haben stets einen Future Value aus einem Present Value errechnet. Natürlich funktioniert dies auch in entgegengesetzter Richtung. Dann lauten die Fragestellung eher so:

"Wieviel muß ich heute investieren, um in Zukunft eine Summe x zu haben?"

Bezogen auf unser erstes Beispiel oben haben wir 1.060€ Euro nach einem Jahr. Wieviel muß ich also heute investieren, um diese Summe bei einem Zinssatz von 6,00% nach einem Jahr zu haben?

Formelmäßig könnte man das folgendermaßen notieren:
PV=FV/(1+i)^1
=> PV=FV*(1/1+i)^n
=> 1.060*(1/1+0,06)^1
=> 1.000

Hier ist die entsprechende T-SQL Formel:

DECLARE @fv FLOAT
DECLARE @i FLOAT
DECLARE @n FLOAT
SELECT
@fv=1060, @i=0.06, @n=1
SELECT
ROUND(@fv*POWER(1+@i,-@n),2)

-----------------------------------------------------
1000.0

(1 row(s) affected)

Wie man hier bereits sieht, läßt sich diese Formel auch unverändert zur Berechnung anderer Laufzeiten verwenden.

DECLARE @fv FLOAT
DECLARE @i FLOAT
DECLARE @n FLOAT
SELECT
@fv=1338.23, @i=0.06, @n=5
SELECT
ROUND(@fv*POWER(1+@i,-@n),2)

-----------------------------------------------------
1000.0

(1 row(s) affected)

Mathematisch ist es äquivalent, ob ich die n-te Wurzel ziehe oder ^1/n rechne.

Und selbstverständlich kann man auch den Present Value bei häufigerer Anzahl der Zinszahlungen pro Jahr ermitteln.

DECLARE @fv FLOAT
DECLARE @i FLOAT
DECLARE @m FLOAT
DECLARE @n FLOAT
SELECT
@fv=1343.92, @i=0.06, @m=2, @n=5
SELECT
ROUND(@fv*POWER(1+@i/@m,-@m*@n),2)

-----------------------------------------------------
1000.0

(1 row(s) affected)

So, jetzt können wir Present und Future Values hin und zurück berechnen
Zum Abschluß dieser kleinen Einführung wollen wir uns noch einer in der Praxis recht häufig auftretenden Fragestellung widmen. Der leichteren Nachvollziehbarkeit bleiben wir bei unserem Beispiel und den bereits bekannten Ergebnissen.

Angenommen, wir wollen wissen, nach welcher Zeit aus 1.000€ 1.343,92€ werden bei einer jährlichen Verzinsung von 6,00%.

Mathematisch lautet diese Formel:
n= (ln(FV)-ln(PV)/ln(1+i))

DECLARE @pv FLOAT
DECLARE @fv FLOAT
DECLARE @i FLOAT
SELECT
@pv=1000, @fv=1338.23, @i=0.06
SELECT
ROUND((LOG(@fv)-LOG(@pv))/LOG(1+@i),2)

-----------------------------------------------------
5.0

(1 row(s) affected)

Die wahrscheinlich noch interessantere Frage nach der Verzinsung eines Papieres, das ich heute zu 1.000 kaufe und welches in 5 Jahren zu 1.338,23 zurückgezahlt wird, stellen wir hier zurück. Sie ist nicht ganz so trivial zu lösen und wird im einem eigenen Beitrag betrachtet. Diese Frage fällt auch insofern hier aus dem Rahmen, als das die hier gezeigten Formeln ohne weiteres in setbasierten SELECTs eingebaut werden können, während die Frage nach der Vezinsung hingegen einen iterativen Trial- and-Error Prozeß beschreibt.

So, Ende dieser kurzen Einführung in das Thema dieser Kategorie. Mathematik ist nicht jedermanns Sache aber vielleicht hat der eine oder andere Leser ja doch mal Verwendung für die hier beschriebenen Verfahren. :-)

Schaltjahre

Posted on Mär 21, 2005 von in SQL Server

Vorweggeschickt mag man durchaus darüber diskutieren, ob eine eigene Funktion zur Ermittlung eines Schaltjahres im SQL Server zwingend notwendig ist, da die meisten Client Sprachen bereits mit ausgefeilten hochoptimierten Datumsbibliotheken ausgestattet sind und daher deutlich bessere Performance bieten sollten als pseudokompiliertes T-SQL, aber ehrlich gesagt geht es mir jetzt mehr darum, einen Weg zu zeigen, wie eine T-SQL basierte Lösung für dieses Problem aussehen könnte. Und die Entscheidung, ob die vorgeschlagene Lösung eingesetzt wird, mag jeder Leser mit sich selber und seinen Anforderungen ausmachen. :-)

Ganze Geschichte »

Wir basteln uns ein Datum

Posted on Feb 12, 2005 von in SQL Server

Was tun, wenn man den Tag, den Monat und das Jahr fein säuberlich in Einzelteilen zerlegt hat? Schauen wir uns mal verschiedene Methoden an, daraus ein gültiges Datum herzustellen.

Ganze Geschichte »

First() und Last() in T-SQL?

Posted on Jan 18, 2005 von in SQL Server

First() und Last() in T-SQL?

Sehr häufig kann man Fragen in SQL Server Communities beobachten von Leuten, die Ihre Access Datenbanken auf den SQL Server portieren und anschließend versuchen, die proprietäre Access SQL Syntax in T-SQL zu konvertieren. Eine der beliebtesten Fragen in diesem Zusammenhang, ist die Frage, nach dem T-SQL Äquivalent zu First() und Last(). Nun, einfache Antwort: Es gibt kein direktes Äquivalent! Aber es gibt einen Workaround, um das gleiche Ergebnis zu erzielen:

USE NORTHWIND
GO
SELECT
(SELECT
OrderDate
FROM
Orders
WHERE
OrderID =
(SELECT MIN(OrderID)
FROM Orders)) AS First_Order
,
(SELECT
OrderDate
FROM
Orders
WHERE
OrderID =
(SELECT MAX(OrderID)
FROM Orders)) AS Last_Order

First_Order Last_Order
------------------------------------------------------ -----------------------
1996-07-04 00:00:00.000 1998-05-06 00:00:00.000

(1 row(s) affected)

Informationen über Parameter von Stored Procedures mit T-SQL

Posted on Dez 27, 2004 von in SQL Server

Zu Dokumentationszwecken kann es dann und wann mal nützlich sein, zu wissen, welche Funktion und/oder Stored Procedure welche Parameter verlangt, welche Typ diese Parameter haben usw, usw...

Ganze Geschichte »

JOIN Stolperfallen Teil 1

Posted on Dez 27, 2004 von in SQL Server

Gelegentlich fragt man sich, ob man wirklich so oft auf die Tastatur hämmern muß, oder ob man sich nicht das eine oder andere Zeichen oder Wort sparen kann. Klingt bekannt? Nun, zumindest mir geht es so. Generell ist das auch so in Ordnung, nur manchmal kann man dabei auch auf die Nase fallen. Wenn dann noch eine merkwürdige Syntaxauslegung ins Spiel kommt, wird es richtig interessant. Beispiel:

SELECT
 r.royalty
 , t.title
 , t.type
 , t.price
FROM
 roysched r
INNER JOIN
 titles t
ON
 r.title_id = t.title_id
ORDER BY
 r.royalty

Die Ergebnismenge wird hier nicht wiedergegeben. Das obige Beispiel läßt sich folgendermaßen "vereinfachen":

SELECT
 r.royalty
 , t.title
 , t.type
 , t.price
FROM
 roysched r
JOIN
 titles t
ON
 r.title_id = t.title_id
ORDER BY
 r.royalty

Das INNER Schlüsselwort kann man weglassen, da der INNER JOIN der Standard JOIN Type des SQL Servers ist. So weit, so gut!

Schaut man sich den Ausführungsplan an, sieht man, daß SQL Server NESTED LOOPS verwendet. Nun sind NESTED LOOPS gerade bei umfangreichen Abfragen nicht gerade optimal und darum kommt man vielleicht auf die Idee, den Server einen kleinen Hinweis mit auf den Weg zu geben, wie er den JOIN verarbeiten soll. Also schreibt man:

SELECT
 r.royalty
 , t.title
 , t.type
 , t.price
FROM
 roysched r
MERGE JOIN
 titles t
ON
 r.title_id = t.title_id
ORDER BY
 r.royalty

Was passiert?

Server: Nachr.-Nr. 170, Schweregrad 15, Status 1, Zeile 8
Zeile 8: Falsche Syntax in der Nähe von 'MERGE'.

Was soll das denn jetzt? Sieht doch syntaktisch einwandfrei aus. Ein Blick in BOL bestätigt dies. Warum also wird das Statement nicht ausgeführt???

Der Grund liegt darin, daß SQL Server bei der Verwendung des MERGE Hinweises (oder jedes anderen JOIN Hints) zwingend auf das INNER Schlüsselwort besteht. Ohne dem geht hier gar nichts. SQL Server ist hier nicht in der Lage, den INNER JOIN als Standard JOIN Typ anzuwenden. Also doch wieder ein paar Mal mehr auf die Tastatur hämmern

SELECT
 r.royalty
 , t.title
 , t.type
 , t.price
FROM
 roysched r
INNER MERGE JOIN
 titles t
ON
 r.title_id = t.title_id
ORDER BY
 r.royalty

Ah, unsere 86 Zeilen kommen zurück und der Ausführungsplan zeigt den MERGE JOIN an. "By design", also nicht wundern oder nachfragen.

Nichtnumerische Zeichen aus einem String entfernen

Posted on Nov 26, 2004 von in SQL Server

Das Problem hat wahrscheinlich jeder schon einmal gehabt. Man stellt Importanforderungen auf, und die Anwender kümmern sich nicht darum und liefern anstelle von sauber getrennten Strings und Zahlen einen bunten Mischmasch aus beidem.

Ganze Geschichte »