Hierarchie in flache Tabelle

07/10/2009 - 09:15 von Thomas Schremser | Report spam
Hallo allerseits!

Ich habe folgende zwei Tabellen:

CREATE TABLE Fach
(
FachID int NOT NULL PRIMARY KEY,
Code varchar(5) NOT NULL,
Von datetime NOT NULL,
Bis datetime NULL
);

CREATE TABLE Zusatz
(
ZusatzID int NOT NULL PRIMARY KEY,
FachID int NOT NULL FOREIGN KEY REFERENCES Fach(FachID),
Code varchar(5) NOT NULL,
Von datetime NOT NULL,
Bis datetime NULL
);

Zu jedem Fach können ein oder mehrere Datensàtze in der Tabelle Zusatz
vorhanden sein. Der Zeitraum eines Zusatzes entspricht entweder exakt dem
des Fachs oder ist ein Teilbereich davon.

Hier ein paar Beispieldaten:

INSERT INTO Fach VALUES(1, 'kiia', '19721001', '19800522');
INSERT INTO Fach VALUES(2, 'ki', '19800523', NULL);
INSERT INTO Fach VALUES(3, 'intia', '19890701', '20010629');
INSERT INTO Fach VALUES(4, 'int', '20010630', NULL);
INSERT INTO Fach VALUES(5, 'uro', '19750301', NULL);

INSERT INTO Zusatz VALUES(1, 2, 'int', '20001231', '20070131');
INSERT INTO Zusatz VALUES(2, 2, 'npi', '20070201', NULL);
INSERT INTO Zusatz VALUES(3, 4, 'hàonk', '20010630', NULL);

Für eine Legacy-Applikation benötige ich nun einen View, der mir diese
Tabellen zu einer flachen Tabelle zusammenfaßt. Das Fach ohne Zusatz soll
dabei nur dann als eigener Datensatz aufscheinen, wenn es entweder überhaupt
keinen Zusatz gibt oder kein Zusatz den exakt gleichen Zeitraum abdeckt.

Das ist das Statement, das ich bisher habe und das Ergebnis davon:

WITH
Faecher AS
(
SELECT
F.Code AS Fach,
F.Von AS FachVon,
F.Bis AS FachBis,
'' AS Zusatz,
NULL AS ZusatzVon,
NULL AS ZusatzBis
FROM
Fach F
UNION SELECT
F.Code AS Fach,
F.Von AS FachVon,
F.Bis AS FachBis,
Z.Code AS Zusatz,
Z.Von AS ZusatzVon,
Z.Bis AS ZusatzBis
FROM
Fach F
INNER JOIN Zusatz Z
ON Z.FachID = F.FachID
)
SELECT
ROW_NUMBER() OVER
(
ORDER BY
FachID,
FachVon,
ZusatzVon
) AS Nr,
Fach,
CONVERT(varchar(10), FachVon, 104) AS FachVon,
CONVERT(varchar(10), FachBis, 104) AS FachBis,
Zusatz,
CONVERT(varchar(10), ZusatzVon, 104) AS ZusatzVon,
CONVERT(varchar(10), ZusatzBis, 104) AS ZusatzBis
FROM
Faecher F
ORDER BY
Nr;

Nr Fach FachVon FachBis Zusatz ZusatzVon ZusatzBis
1 kiia 01.10.1972 22.05.1980 NULL NULL
2 ki 23.05.1980 NULL NULL NULL
3 ki 23.05.1980 NULL int 31.12.2000 31.01.2007
4 ki 23.05.1980 NULL npi 01.02.2007 NULL
5 intia 01.07.1989 29.06.2001 NULL NULL
6 int 30.06.2001 NULL NULL NULL
7 int 30.06.2001 NULL hàonk 30.06.2001 NULL
8 uro 01.03.1975 NULL NULL NULL

Der Datensatz mit der Nr. 6 ist hier überflüssig, weil Nr. 7 den selben
Zeitraum abdeckt. Wie bekomme ich den 6-er weg?

TIA

Grüße
Thomas

Any problem in computer science can be solved with another layer
of indirection. But that usually will create another problem.
David Wheeler
 

Lesen sie die antworten

#1 Christoph Ingenhaag
07/10/2009 - 11:42 | Warnen spam
Hallo Thomas!

"Thomas Schremser" wrote:

Hallo allerseits!

Ich habe folgende zwei Tabellen:

CREATE TABLE Fach
(
FachID int NOT NULL PRIMARY KEY,
Code varchar(5) NOT NULL,
Von datetime NOT NULL,
Bis datetime NULL
);

CREATE TABLE Zusatz
(
ZusatzID int NOT NULL PRIMARY KEY,
FachID int NOT NULL FOREIGN KEY REFERENCES Fach(FachID),
Code varchar(5) NOT NULL,
Von datetime NOT NULL,
Bis datetime NULL
);

Zu jedem Fach können ein oder mehrere Datensàtze in der Tabelle Zusatz
vorhanden sein. Der Zeitraum eines Zusatzes entspricht entweder exakt dem
des Fachs oder ist ein Teilbereich davon.

Hier ein paar Beispieldaten:

INSERT INTO Fach VALUES(1, 'kiia', '19721001', '19800522');
INSERT INTO Fach VALUES(2, 'ki', '19800523', NULL);
INSERT INTO Fach VALUES(3, 'intia', '19890701', '20010629');
INSERT INTO Fach VALUES(4, 'int', '20010630', NULL);
INSERT INTO Fach VALUES(5, 'uro', '19750301', NULL);

INSERT INTO Zusatz VALUES(1, 2, 'int', '20001231', '20070131');
INSERT INTO Zusatz VALUES(2, 2, 'npi', '20070201', NULL);
INSERT INTO Zusatz VALUES(3, 4, 'hàonk', '20010630', NULL);

Für eine Legacy-Applikation benötige ich nun einen View, der mir diese
Tabellen zu einer flachen Tabelle zusammenfaßt. Das Fach ohne Zusatz soll
dabei nur dann als eigener Datensatz aufscheinen, wenn es entweder überhaupt
keinen Zusatz gibt oder kein Zusatz den exakt gleichen Zeitraum abdeckt.

Das ist das Statement, das ich bisher habe und das Ergebnis davon:

WITH
Faecher AS
(
SELECT
F.Code AS Fach,
F.Von AS FachVon,
F.Bis AS FachBis,
'' AS Zusatz,
NULL AS ZusatzVon,
NULL AS ZusatzBis
FROM
Fach F
UNION SELECT
F.Code AS Fach,
F.Von AS FachVon,
F.Bis AS FachBis,
Z.Code AS Zusatz,
Z.Von AS ZusatzVon,
Z.Bis AS ZusatzBis
FROM
Fach F
INNER JOIN Zusatz Z
ON Z.FachID = F.FachID
)
SELECT
ROW_NUMBER() OVER
(
ORDER BY
FachID,
FachVon,
ZusatzVon
) AS Nr,
Fach,
CONVERT(varchar(10), FachVon, 104) AS FachVon,
CONVERT(varchar(10), FachBis, 104) AS FachBis,
Zusatz,
CONVERT(varchar(10), ZusatzVon, 104) AS ZusatzVon,
CONVERT(varchar(10), ZusatzBis, 104) AS ZusatzBis
FROM
Faecher F
ORDER BY
Nr;

Nr Fach FachVon FachBis Zusatz ZusatzVon ZusatzBis
1 kiia 01.10.1972 22.05.1980 NULL NULL
2 ki 23.05.1980 NULL NULL NULL
3 ki 23.05.1980 NULL int 31.12.2000 31.01.2007
4 ki 23.05.1980 NULL npi 01.02.2007 NULL
5 intia 01.07.1989 29.06.2001 NULL NULL
6 int 30.06.2001 NULL NULL NULL
7 int 30.06.2001 NULL hàonk 30.06.2001 NULL
8 uro 01.03.1975 NULL NULL NULL

Der Datensatz mit der Nr. 6 ist hier überflüssig, weil Nr. 7 den selben
Zeitraum abdeckt. Wie bekomme ich den 6-er weg?

TIA

Grüße
Thomas

Any problem in computer science can be solved with another layer
of indirection. But that usually will create another problem.
David Wheeler




das ist unklar:
"Das Fach ohne Zusatz soll dabei nur dann als eigener Datensatz aufscheinen,
wenn es entweder überhaupt keinen Zusatz gibt oder kein Zusatz den exakt
gleichen Zeitraum abdeckt."

Wo ist der Unterschied zwischen "ohne Zusatz" und "keinen Zusatz"?
Ich würde sagen ein Fach ohne Zusatz hat keinen Zusatz...

Btw. Wieso kein left join in der CTE?

Viele Grüße
Christoph

Ähnliche fragen