nicht angemeldet
SQL-Query mit Fallunterscheidungen
Hallo zusammen,
ich habe eine recht komplexe Abfrage zu machen und weiß nicht, wie ich das am besten hinbekomme.
Also:
Ich habe eine Tabelle, wo Vertragsdaten von Personen drinstehen. Jede Person kann beliebig viele Verträge haben.
Je Vertrag gibt es ein Beginndatum, ein Endedatum, und zusätzlich noch je ein Korrekturfeld für das Beginn- und Endedatum, das aber nicht in jedem Fall gefüllt ist.
Z.B.:
pn | beginn | ende | beginn_korr | ende_korr
------------------------------------------------------
1 | 01.02.2000 | 31.07.2003 | NULL | NULL
1 | 01.08.2003 | 15.01.2004 | NULL | 31.01.2004
...
Ich brauche jetzt für jede Person die Summe ihrer Vertragszeiten, also prinzipiell:
SELECT SUM(DATEDIFF(day, beginn, ende)), pn
FROM tabelle
GROUP by pn
Das Problem sind jetzt aber die "Sonderfälle":
1. Wenn ein Korrekturfeld gefüllt ist, ist dieses zu verwenden (also "beginn_korr" statt "beginn", bzw. "ende_korr" statt "ende")
2. Liegt das Vertragsende in der Zukunft und zwar nach dem nächsten Schulhalbjahresbeginn (1.2. / 1.8.), dann ist der Schulhalbjahresbeginn als Endedatum zu nehmen.
Der "nächste" Schulhalbjahresbeginn ist vom aktuellen Datum aus zu sehen. Z.Zt. ist es der 1.2.08, ab Februar 2008 ist es dann der 1.8.08, usw.
***
Wie kann ich diese Query formulieren?
(MS SQL Server 2005)
Hier hatte ich bereits Hilfe dabei erhalten, das nächste Schulhalbjahr zu bestimmen: http://forum.de.selfhtml.org/archiv/2007/9/t159260/
Danke im Voraus für eure Hilfe!
Sam
-
yo,
zu 1, es sollte unter MSSQL eine funktion ISNULL gegeben, die du für die korrekturwerte benutzen kannst. Beispiel: ISNULL(beginn_korr, beginn). die funktion bewirkt, dass er immer die korrektur-spalte nimmt, es sei den sie ist NULL, dann nimmt er den jeweiligen anderen spaltenwert.
zu 2, würde sich die CASE klausel anbieten, mit der du prüfen kannst, ob das vertrangsende in der zukunft liegt > GEDDATE() und dann entsprechend wie gewünscht verzweigen kannst.
Ilja
-
zu 1, es sollte unter MSSQL eine funktion ISNULL gegeben, die du für die korrekturwerte benutzen kannst. Beispiel: ISNULL(beginn_korr, beginn). die funktion bewirkt, dass er immer die korrektur-spalte nimmt, es sei den sie ist NULL, dann nimmt er den jeweiligen anderen spaltenwert.
wie cool, was es alles gibt ...! ;-)
zu 2, würde sich die CASE klausel anbieten, mit der du prüfen kannst, ob das vertrangsende in der zukunft liegt > GEDDATE() und dann entsprechend wie gewünscht verzweigen kannst.
Ilja
Danke dir,
das hilft schonmal. Mal sehen, wie weit ich nun komme ... -
yo,
yo yo :)
Ich würde die verwendung der COALESCE(wert1, wert2, wert3) empfehlen, da man sie mit "beliebig" vielen Werten füttern kann und sie den ersten nimmt, der nicht NULL ist. ISNULL ist da eher beschränkt. ;)
GEDDATE()
GETDATE() funktioniert eher, glaub ich ;)
Ciao, Frank
-
yo Frank,
Ich würde die verwendung der COALESCE(wert1, wert2, wert3) empfehlen, da man sie mit "beliebig" vielen Werten füttern kann und sie den ersten nimmt, der nicht NULL ist. ISNULL ist da eher beschränkt. ;)
ja, ist sicherlich auch eine gute option
GETDATE() funktioniert eher, glaub ich ;)
jepp, da war doch mal wieder der tippfehler drinne, ich finde SYSDATE sowieso besser... ;-)
Ilja
-
Servus,
als ersten Schritt habe ich nun mal den Fall, dass das Endedatum VOR dem nächsten Halbjahresbeginn liegt (es wird also die Zeitspanne Vertragsbeginn-Vertragsende berechnet und summiert).
Wie bekomme ich da nun die CASE-Verzweigung rein?
SELECT SUM(DATEDIFF(day, isnull(tabelle.beginn_korrektur,tabelle.beginn),isnull(tabelle.ende_korrektur,tabelle.ende))) AS VertrTage, tabelle.pn FROM tabelle WHERE (tabelle.ende <= ( SELECT next_hj = CASE WHEN SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112),5, 4) < '0201' THEN CONVERT(DATETIME, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '0201', 112) WHEN SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112),5, 4) < '0801' THEN CONVERT(DATETIME, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '0801', 112) ELSE CONVERT(DATETIME, CAST(YEAR(GETDATE()) + 1 AS VARCHAR(4)) + '0201', 112) END ) ) GROUP BY tabelle.pn-
yo,
Wie bekomme ich da nun die CASE-Verzweigung rein?
die case verzweigung gehört in deinem falle nicht in die WHERE klausel rein, diese sorgt ja nur für eine filterung der datensätze, die in die ergebnismenge kommen. fachlich spricht man dann von der Selektion durch die WHERE klausel.
was du tun musst ist, die Case-fall-Unterscheidung in der Projektion mit reinzunehmen, als bei den daten, die ausgegeben werden sollen. also nicht in die WHERE klausel, sondern in die SELECT klausel (um noch ein wenig zu verwirren, das "normale" abfragen SELECT beinhaltet quasi die Projektion und Selektion in einem, also vertikale und horzontale ausgabe, die selektion in der where klausel und die projektion nach der select klausel. aber das verwirrt wohl mehr als es erklärt).
wie auch immer, ich sag es mal umgangssprachlich, die case bedingung muss nach oben in den ausgabenteil.
Ilja
-
Hi,
mir fehlt gerade etwas das Einfühlungsvermögen. Könntest du mal ein Datenbeispiel (Input und gewünschter Output) mit diesem Schuljahrbeginn geben?
SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112),5, 4) < '0201'
Wenn Substring nun mal (theoretischerweise) '201' ergibt, dann geht die Verzweigung nicht mehr so gut auf. Es ist ungeeignet Datumsangaben als (Teil)Strings miteinander zu vergleichen. Nutze dazu besser die Funktionen wie DAY, MONTH, YEAR, DATEPART usw
Wir haben heute den 4.10., das ist grösser als 1.8. weil der Monat grösser ist, oder. D.h. du könntest jetzt einfach das nächste Schulhalbjahrbeginndatum ermitteln:
-- @newDate ist eine Variable DECLARE @newDate datetime SELECT @newDate = CASE WHEN MONTH(GETDATE()) > 8 THEN CONVERT(datetime, CAST((YEAR(GETDATE()) + 1) as varchar(4)) + '-02-01', 120) ELSE CONVERT(datetime, CAST(YEAR(GETDATE()) as varchar(4)) + '-08-01', 120) ENDStatt GETDATE() kannst du natürlich auch die ende oder ende_korr oder COALESCE(ende_korr, ende) einsetzen. Und irgendwie müsstest du auch noch ermitteln ob das ende in der Zukunft liegt, was aber relativ trivial ausfallen dürfte.
Cheers, Frank
-
super, danke, ich hab jetzt die Query am laufen ... Yessssss! ;-)
Wir haben heute den 4.10., das ist grösser als 1.8. weil der Monat grösser ist, oder. D.h. du könntest jetzt einfach das nächste Schulhalbjahrbeginndatum ermitteln:
-- @newDate ist eine Variable
DECLARE @newDate datetime
SELECT @newDate =
CASE
WHEN MONTH(GETDATE()) > 8
THEN CONVERT(datetime,
CAST((YEAR(GETDATE()) + 1) as varchar(4)) + '-02-01',
120)
ELSE CONVERT(datetime,
CAST(YEAR(GETDATE()) as varchar(4)) + '-08-01',
120)
END> Hier fehlt aber noch der Fall, dass wir Januar haben und der 1.2. des selben Jahres das gesuchte Datum ist ... Aber das krieg ich hin ;-) Vielen Dank!!!!!! Sam
-
-
-
-
-