Erstellen einer Datumstabelle anhand von Vorgaben

Question

Guten Tag,

ich hoffe ich bin hier richtig, durch google bin ich auf das Forum gestoßen, ich hoffe auf regen Austausch. :-)

Ich definiere mal die gegebenen Daten:

Select1:

Select t1.ID, t1.DATUMVON, t1.DATUMBIS, t1.PREIS from t1

Ausgabe:

ID    VON         BIS        PREIS
1  25.07.2013    30.12.2013  17,80
1  03.01.2014    06.01.2014  18,90

Select 2:

Select t2.ID, t2.VON, t2.BIS, t2.PREIS from t2

Ausgabe:

ID     VON            BIS         PREIS
1     25.09.2013   24.10.2013      1,10 
1     25.10.2013   25.10.2013      1,61
1     04.11.2013   23.12.2013      1,90
1     30.12.2013   10.01.2014      2,70

Jetzt sollen diese beiden Selects so miteinander verknüpft werden, dass folgendes bei herauskommt:

ID        VON          BIS        PREIS
1      25.07.2013   24.09.2013    17,80
1      25.09.2013   24.10.2013    18,90 (17,80 + 1,1)
1      25.10.2013   03.11.2013    19,41 (17,80 + 1,61)
1      04.11.2013   29.12.2013    19,70 (17,80 + 1,90)
1      30.12.2013   05.01.2013    20,50 (17,80 + 2,70)
1      06.01.2014   10.01.2014    21,50 (18,80 + 2,70)

Kann mir jemand hier eine Hilfestellung geben? Ich verzweifel mittlerweile 2 Tage dran.

Danke!

Erik

Answer 1

Hallo Erik,

ganz lösen kann ich es nicht, aber die gewünschte Hilfestellung geben: JOIN die beiden Tabellen, andere Alternative wäre ein Subquery.

SET LANGUAGE deutsch;

DECLARE @t1 AS Table (ID int, VON date, BIS date, PREIS money);
DECLARE @t2 AS Table (ID int, VON date, BIS date, PREIS money);

INSERT INTO @t1 VALUES (1, '25.07.2013', '30.12.2013',  17.80);
INSERT INTO @t1 VALUES (1, '03.01.2014', '06.01.2014',  18.90);

INSERT INTO @t2 VALUES (1, '25.09.2013', '24.10.2013', 1.10);
INSERT INTO @t2 VALUES (1, '25.10.2013', '25.10.2013', 1.61);
INSERT INTO @t2 VALUES (1, '04.11.2013', '23.12.2013', 1.90);
INSERT INTO @t2 VALUES (1, '30.12.2013', '10.01.2014', 2.70);


SELECT t1.*
      ,t1.PREIS + ISNULL(t2.PREIS, 0) AS PreisNeu
FROM @t1 AS t1
     LEFT JOIN
     @t2 AS t2
         ON t1.ID = t2.ID
            AND t1.VON <= t2.VON
            AND t1.BIS >= t2.BIS

Nur die Datumsbereiche aufzusplitten, wenn für ein Teilbereich keine Daten in t2 vorhanden ist, ist nicht so einfach.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hallo Olaf,

danke für deine Antwort. Bin schon einen Schritt weiter, danke für den Hinweis. Aktuell ergibt sich folgendes:

Select t1.*, t2.*, iif(t2.VON >= t1.VON and t2.BIS <= t1.BIS, t1.PREIS+t2.PREIS, t1.PREIS) as PreisG
from
(Select t5.ID, min(t5.VON) as VON, max(t5.BIS) as BIS, t5.PREIS from t5 where t5.NR = 101 group by t5.ID, t5.PREIS)t1
left join
(Select t5.ID, min(t5.VON) as VON, max(t5.BIS) as BIS, t5.PREIS from t5 where t5.NR = 610 group by t5.ID, t5.PREIS)t2 on t1.ID = t2.ID and t1.VON <= t2.VON and t2.BIS <= t1.BIS

Als Ergebis erhalte ich:

ID	VON	                      BIS        	  PREIS  	ID	VON	                     BIS	         PREIS	         PREISG
1	25.07.2013 00:00	30.12.2013 00:00	 17,80 € 	1	05.09.2013 00:00	24.10.2013 00:00	 1,10 € 	 18,90 € 
1	25.07.2013 00:00	30.12.2013 00:00	 17,80 € 	1	25.10.2013 00:00	25.10.2013 00:00	 1,61 € 	 19,41 € 
1	25.07.2013 00:00	30.12.2013 00:00	 17,80 € 	1	04.11.2013 00:00	23.12.2013 00:00	 1,90 € 	 19,71 € 
1	06.01.2014 00:00	10.01.2014 00:00	 18,80 € 					                                                         18,90 €

Sieht schon gut aus, was fehlt, ist die Variante ganz unten, dort müssten 18,80 + 2,70 gerechnet werden.

Absegesehen davon, stimmen die Datumsangaben noch nicht.

Answer 3

Bei meinem Beispiel reicht eine Erweiterung der JOIN Logik mit OR:

SET LANGUAGE deutsch;

DECLARE @t1 AS Table (ID int, VON date, BIS date, PREIS money);
DECLARE @t2 AS Table (ID int, VON date, BIS date, PREIS money);

INSERT INTO @t1 VALUES (1, '25.07.2013', '30.12.2013',  17.80);
INSERT INTO @t1 VALUES (1, '03.01.2014', '06.01.2014',  18.90);

INSERT INTO @t2 VALUES (1, '25.09.2013', '24.10.2013', 1.10);
INSERT INTO @t2 VALUES (1, '25.10.2013', '25.10.2013', 1.61);
INSERT INTO @t2 VALUES (1, '04.11.2013', '23.12.2013', 1.90);
INSERT INTO @t2 VALUES (1, '30.12.2013', '10.01.2014', 2.70);


SELECT t1.*
      ,t1.PREIS + ISNULL(t2.PREIS, 0) AS PreisNeu
      ,t2.*
FROM @t1 AS t1
     LEFT JOIN
     @t2 AS t2
         ON t1.ID = t2.ID
            AND (t1.VON <= t2.VON AND t1.BIS >= t2.BIS
                 OR t1.VON >= t2.VON AND t1.BIS <= t2.BIS)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

Hallo Olaf,

danke, manchmal ist eine Klammer sowas von richtungsweisend :-)

Habe mir mit UNION ALL jetzt das so zusammengebastelt, dass es wie folgt aussieht:

ID      VON                     BIS                     PREIS
1	2013-07-25 00:00:00	2013-12-30 00:00:00	17.8
1	2013-07-25 00:00:00	2013-12-30 00:00:00	17.8
1	2013-07-25 00:00:00	2013-12-30 00:00:00	17.8
1	2014-01-06 00:00:00	2014-01-10 00:00:00	18.8
1	2013-09-05 00:00:00	2013-10-24 00:00:00	1.1
1	2013-10-25 00:00:00	2013-10-25 00:00:00	1.61
1	2013-11-04 00:00:00	2013-12-23 00:00:00	1.9
1	2013-12-30 00:00:00	2014-01-10 00:00:00	2.7

Jetzt ist die Kunst, dass die Ausgabe wie folgt aussieht

ID     VON                      BIS                    PREISG
1	2013-07-25 00:00:00	2013-09-04 00:00:00	17.80 (nur 17,80)
1	2013-09-05 00:00:00	2013-10-24 00:00:00	18.90 (17,80 + 1,10)
1	2013-10-25 00:00:00	2013-10-25 00:00:00	19.41 (17,80 + 1,61)
1	2013-11-04 00:00:00	2013-12-23 00:00:00	19.70 (17,80 + 1,90)
1	2013-12-30 00:00:00	2014-01-05 00:00:00	20.50 (17,80 + 2,70)
1	2014-06-01 00:00:00	2014-01-10 00:00:00	21.50 (18,80 + 2,70)
Kannst du mir hierbei auch noch bitte behilflich sein? Die Wenn Logik scheint mir klar, nur der Ansatz 
zum darstellen der forlaufenden Angaben fehlt mir...
Danke
Erik

Answer 5

Meinst Du so?

SELECT t1.ID
      ,CASE WHEN t1.VON > t2.VON 
            THEN T1.VON
            ELSE t2.VON END AS VONneu
      ,CASE WHEN t1.BIS < t2.BIS 
            THEN T1.BIS
            ELSE t2.BIS END AS BISneu
      ,CASE WHEN t2.PREIS IS NULL
            THEN CONVERT(varchar(10), t1.PREIS) 
                 + ' (nur ' + CONVERT(varchar(10), t1.PREIS)
            ELSE CONVERT(varchar(10), t1.PREIS + t2.PREIS) 
                 + ' (' + CONVERT(varchar(10), t1.PREIS) + ' + ' + CONVERT(varchar(10), t2.PREIS)
            END AS PREISG
FROM @t1 AS t1
     LEFT JOIN
     @t2 AS t2
         ON t1.ID = t2.ID
            AND (t1.VON <= t2.VON AND t1.BIS >= t2.BIS
                 OR t1.VON >= t2.VON AND t1.BIS <= t2.BIS)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 6

Hallo Olaf,

danke für das Statement.

Kannst du mir bitte erklären, was

,CASE WHEN t2.PREIS IS NULL
            THEN CONVERT(varchar(10), t1.PREIS) 
                 + ' (nur ' + CONVERT(varchar(10), t1.PREIS)
            ELSE CONVERT(varchar(10), t1.PREIS + t2.PREIS) 
                 + ' (' + CONVERT(varchar(10), t1.PREIS) + ' + ' + CONVERT(varchar(10), t2.PREIS)
            END AS PREISG

also Ausgabe ergeben soll? Ich wandele die ganzen MSSQL-Statements auf Firebird um. Aber ich kann nicht ganz nachvollziehen, wie das Ergebnis aussehen soll bei PreisG

Danke!

Erik

Answer 7

Hallo Erik,

das ist dazu da, das gewünschte Ergebnis

17.80 (nur 17,80)
18.90 (17,80 + 1,10)

für den Preis zu formatieren; then WHEN IS NULL Part, um das "..(nur ...)" zu erzeugen, ansonsten detailiert den Preis + "Zuschlag".

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 8

Hallo Olaf,

vielen Dank für die Hilfe + Erklärung. Wieder etwas schalauer geworden :-)

Was aber nicht funktioniert, ist dass die Datumsangaben richtig berechnet werden, denn der Preis 17,80 ist ja eigentlich "nur" bis 04.09.2013 gültig, denn ab 05.09.2013 galt ja der Zuschlag von 1,10. also müsste ausgewertet werden:

23.07.2013 - 04.09.2013 17,80 €

05.09.2013 - 24.10.2013 18,90 € (17,80+1,10)

Weißt du hierfür noch einen Lösungsansatz?

Danke

Erik

Answer 9

Hallo Erik,

Wenn Dir die Antwort von Olaf geholfen hat, dann bitte markiere diese als Antwort.

Danke und Gruss,

Ionut

---

Ionut Duma, MICROSOFT   Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip„Entwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

Answer 10

Hallo Erik,

Ich gehe davon aus, dass die Antwort Dir weitergeholfen hat.
Wenn nicht, neue Rückfragen oder Ergänzungen zu diesem Thread bleiben weiterhin möglich.

Danke und viele Grüße,
Ionut

---

Ionut Duma, MICROSOFT   Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip„Entwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.