Zusammenhängende Datumsbereiche ermitteln

Question

Hallo,

ich steh' mal wieder auf dem Schlauch :-(

Gegeben ist eine Tabelle

CREATE TABLE HIST(
EKEY INT,
dtGueltigAb DATETIME,
dtGueltigBis DATETIME
)

mit folgenden Daten

INSERT HIST(EKEY,dtGueltigAb,dtGueltigBis) VALUES (3,CONVERT(DATETIME,'2002-01-01 00:00:00',102),CONVERT(DATETIME,'2002-12-31 00:00:00',102))
INSERT HIST(EKEY,dtGueltigAb,dtGueltigBis) VALUES (3,CONVERT(DATETIME,'2001-01-01 00:00:00',102),CONVERT(DATETIME,'2001-12-31 00:00:00',102))
INSERT HIST(EKEY,dtGueltigAb,dtGueltigBis) VALUES (3,CONVERT(DATETIME,'2000-01-01 00:00:00',102),CONVERT(DATETIME,'2000-12-31 00:00:00',102))
INSERT HIST(EKEY,dtGueltigAb,dtGueltigBis) VALUES (1,CONVERT(DATETIME,'2000-09-01 00:00:00',102),CONVERT(DATETIME,'2000-11-30 00:00:00',102))
INSERT HIST(EKEY,dtGueltigAb,dtGueltigBis) VALUES (1,CONVERT(DATETIME,'2000-07-01 00:00:00',102),CONVERT(DATETIME,'2000-08-31 00:00:00',102))
INSERT HIST(EKEY,dtGueltigAb,dtGueltigBis) VALUES (1,CONVERT(DATETIME,'2000-04-01 00:00:00',102),CONVERT(DATETIME,'2000-05-31 00:00:00',102))
INSERT HIST(EKEY,dtGueltigAb,dtGueltigBis) VALUES (1,CONVERT(DATETIME,'2000-01-01 00:00:00',102),CONVERT(DATETIME,'2000-03-31 00:00:00',102))

Jetzt benötige ich den EKEY, MIN(dtGueltigAb), MAX(dtGueltigBis) aus den Datensätzen, welche ein DateDiff(DD,dtGueltigBis, dtGueltigAb) nicht größer als 2 haben sowie der EKEY = 1 ist und bsplw. das Datum 1.11.2000 zwischen MIN(dtGueltigVon) und MAX(dtGueltigBis) liegt. Als Ergebis also  1,'2000-07-01 00:00:00','2000-11-30 00:00:00'.

Danke für Eure Hilfe,

Klaus

---

No Brain - No Pain

Answer 1

Hallo Klaus,

nach deinen Kriterien werden erst alle Datensätze durch die WHERE Klausel zurückgegeben, da DATEDIFF( DD, dtGueltigBis, dtGueltigAb ) in allen Fällen negativ (also kleiner oder gleich 2) ist.

SELECT   EKEY,
         dtGueltigAb,
         dtGueltigBis,
         DATEDIFF( DD, dtGueltigBis, dtGueltigAb )
FROM     HIST

Durch die Gruppierung und die anschließende Einschränkung des Datumsbereichs kommen dann zwei Ergebnisse. Aber nicht das, was Du erwartest.

SELECT   EKEY,
         MIN( dtGueltigAb ),
         MAX( dtGueltigBis )
FROM     HIST
WHERE    DATEDIFF( DD, dtGueltigBis, dtGueltigAb ) <= 2
GROUP BY EKEY
HAVING   '2000-11-01T00:00:00' BETWEEN MIN( dtGueltigAb ) AND MAX( dtGueltigBis )

Daher die Frage: Sind die genannten Abfragekriterien wirklich richtig?

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 2

>Daher die Frage: Sind die genannten Abfragekriterien wirklich richtig?

Ja, leider. Laut meinem Beispiel kämen ja nur 2 Datensätze in Frage, welche lt. DateDiff <= 2 zusammenhängen

INSERT HIST(EKEY,dtGueltigAb,dtGueltigBis) VALUES (1,CONVERT(DATETIME,'2000-09-01 00:00:00',102),CONVERT(DATETIME,'2000-11-30 00:00:00',102))
INSERT HIST(EKEY,dtGueltigAb,dtGueltigBis) VALUES (1,CONVERT(DATETIME,'2000-07-01 00:00:00',102),CONVERT(DATETIME,'2000-08-31 00:00:00',102))

und das Datum 1.11.2000 enthalten. Und von denen beiden brauch ich das GültigAb, GültigBis....

Thx,

Klaus

---

No Brain - No Pain

Answer 3

Hallo Klaus,

lt. deinem Beispiel nicht. Da käme ganz was anderes raus :)

DateDiff geht nur auf den einzelnen Datensatz oder eben die Aggregate (MIX, MAX, ...) Aber wenn ich das richtig verstehe, willst Du datensatzübergreifend die Werte zusammenführen, bei denen keine Lücken zwischen dem dtGueltigBis des ersten und dem dtGueltigAb des zweiten Datensatzes besteht?

Ob das so geht, weiß ich grade nicht. Ich schau mir das heute Abend mal nochmal an, wenn die Profis hier nicht schneller sind :)

Auch wenn das so gehen würde, wäre es in deinem Beispiel dennoch so, dass auch EKEY 3 und dort der Range aller drei Datumsbereiche zurückgegeben würde. Falls Du denkst, das soll nicht so sein, erklär mal bitte, warum das so sein soll.

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 4

>Aber wenn ich das richtig verstehe, willst Du datensatzübergreifend die Werte zusammenführen, bei denen keine Lücken zwischen dem dtGueltigBis des ersten >und dem dtGueltigAb des zweiten Datensatzes besteht

Genau so ist es. Und die Range von EKEY 3 sollen nicht mit zurückgegeben werden. Nur die von EKEY 1.

Thx,

Klaus

---

No Brain - No Pain

Answer 5

Hmm, das ist jetzt blöd. Ich habe noch einen weiteren Datensatz in die Testtabelle eingefügt:

INSERT HIST(EKEY,dtGueltigAb,dtGueltigBis) VALUES (1,CONVERT(DATETIME,'2000-12-01 00:00:00',102),CONVERT(DATETIME,'2000-12-15 00:00:00',102))

Wenn ich jetzt mit

select t1.EKEY, MIN(t1.dtGueltigAb),MAX(t2.dtGueltigBis) from 
HIST t1 INNER JOIN HIST t2
on (DATEADD(DAY,1,t1.dtGueltigBis) = t2.dtGueltigAb)
WHERE 
t1.EKEY = 1 AND 
CONVERT(DATETIME,'2000-11-01 00:00:00',102) BETWEEN t1.dtGueltigAb AND t2.dtGueltigBis
GROUP BY t1.EKEY

eine Abfrage ausführe, bekomme ich das korrekte Ergebnis

EKEY (No column name) (No column name)
1 2000-07-01 00:00:00.000 2000-12-15 00:00:00.000

Ändere ich aber das Datum auf den bsplw. 2.7.2000, bekomme ich nicht das maximale dtGueltigbis (2000-12-15 00:00:00.000) sondern nur das des vorletzten Ds (2000-11-30 00:00:00.000) ausgegeben....

Für weitere Tipps dankend,

Klaus

---

No Brain - No Pain

Answer 6

Alles doof! ;-)

Habs mit einem Query nicht hinbekommen. Hab jetzt statt dessen eine Cursor verwendet.

CREATE PROCEDURE getValidityPeriod
@EMPLOYEEKEY INT,
@DATE DATETIME
as
DECLARE @DATEE DATETIME
DECLARE @STARTVON DATETIME
DECLARE @STARTBIS DATETIME
DECLARE @DTVON DATETIME
DECLARE @DTBIS DATETIME
SET @DATEE = @DATE
DECLARE vcursor CURSOR FOR 
SELECT dtGueltigAb, dtGueltigBis FROM HIST WHERE EKEY = @EMPLOYEEKEY ORDER BY dtGueltigAb DESC
OPEN vcursor
RESTART:
FETCH NEXT FROM vcursor INTO @DTVON, @DTBIS
WHILE @@FETCH_STATUS = 0
BEGIN
        if @STARTVON IS NULL
        BEGIN
                        SET @STARTVON = @DTVON;
                        SET @STARTBIS = @DTBIS
        END
        IF DATEDIFF(DAY,@STARTVON,@DTBIS) = -1
        BEGIN
                SET @STARTVON = @DTVON
        END
        ELSE
                BREAK
        FETCH NEXT FROM vcursor INTO @DTVON, @DTBIS
END
IF @DATEE NOT BETWEEN @STARTVON AND @STARTBIS
        BEGIN
                SET @STARTVON = @DTVON
                SET @STARTBIS = @DTBIS
                GOTO RESTART
        END
CLOSE vcursor
DEALLOCATE vcursor
SELECT @STARTVON, @STARTBIS

 Ist nicht schick, sollte aber erstmal funzen. Den Code kann man sicherlich auch noch optimieren.

SG,

Klaus

---

No Brain - No Pain