Tage im Monat mit überlagernden Zeiträumen zählen

Question

Hallo und einen schönen Tag,

ich benötige Ideen für eine SQL-Auswertung.

Ich habe folgende Daten:

IF OBJECT_ID('tempdb..#TagCounter') IS NOT NULL
BEGIN
  DROP TABLE #TagCounter;
END
GO


IF OBJECT_ID('tempdb..#tblDatum') IS NOT NULL
BEGIN
  DROP TABLE #tblDatum;
END
GO


CREATE TABLE #tblDatum(
 Datum smalldatetime not null PRIMARY KEY
)
GO


DECLARE @datum smalldatetime
SET @datum = '20080101'
WHILE @datum < '20101231' BEGIN
 INSERT INTO #tblDatum(Datum)
  VALUES (@datum)
 SET @datum = DateAdd(dd, 1, @datum)
END

GO


CREATE TABLE #TagCounter(
Typ_ID INT,
start DATETIME,
ende DATETIME,
Art INT
);
GO 

INSERT INTO #TagCounter VALUES (1,'2008-19-12','2009-04-01',203);
INSERT INTO #TagCounter VALUES (1,'2009-02-01','2009-16-01',203);
INSERT INTO #TagCounter VALUES (1,'2009-12-01','2009-05-04',203);
INSERT INTO #TagCounter VALUES (1,'2009-03-01','2009-04-02',222);
INSERT INTO #TagCounter VALUES (1,'2009-07-01','2009-09-01',222);
INSERT INTO #TagCounter VALUES (2,'2009-20-01','2009-25-01',222);
INSERT INTO #TagCounter VALUES (2,'2009-01-01','2009-31-01',222);
INSERT INTO #TagCounter VALUES (2,'2009-01-01','2009-19-02',222);
INSERT INTO #TagCounter VALUES (2,'2008-22-05','2008-19-02',203);
INSERT INTO #TagCounter VALUES (3,'2008-20-07','2009-25-01',222);
INSERT INTO #TagCounter VALUES (3,'2009-12-01','2009-31-01',222);
INSERT INTO #TagCounter VALUES (3,'2009-12-02','2009-19-02',222);
INSERT INTO #TagCounter VALUES (3,'2008-14-05','2006-30-06',203);
INSERT INTO #TagCounter VALUES (3,'2008-19-05','2006-22-05',203);

Nun möchte ich die Summe der Tage im Monat wissen, und zwar je Typ_ID und Art. Dabei dürfen sich überlagernde Zeiträume aber nur einmal gezählt werden.

Beispiel: Der erste Datensatz typ_ID 1, Art 203 läuft bis zum 04.01.2009. Der nächste mit typ_ID 1, Art 203 beginnt am 02.01.2009. Der Zeitraum 02.01. - 04.01. ist bereits erledigt, darf also nicht noch einmal bei der Summe der Tage für den Typ_ID und die Art gezählt werden.

Da es sich um recht viele Datensätze handelt, sollte die Lösung auch möglichst perfomant sein. Eine Hilfstabelle mit Monaten existiert.

Danke vorab und ein schönes Wochenende.

 

Answer 1

servus,

wenn du Tage zählen willst, brauchst du - ich möchte sagen logischerweise - eine Tagestabelle. Im Grunde sieht es so aus:

DECLARE @Sample TABLE (Typ_ID INT, Start DATETIME, Ende DATETIME, Art INT) ;

INSERT INTO @Sample VALUES 
  (1, '2008-19-12', '2009-04-01', 203),
  (1, '2009-02-01', '2009-16-01', 203),
  (1, '2009-12-01', '2009-05-04', 203),
  (1, '2009-03-01', '2009-04-02', 222),
  (1, '2009-07-01', '2009-09-01', 222),
  (2, '2009-20-01', '2009-25-01', 222),
  (2, '2009-01-01', '2009-31-01', 222),
  (2, '2009-01-01', '2009-19-02', 222),
  (2, '2008-22-05', '2008-19-02', 203),
  (3, '2008-20-07', '2009-25-01', 222),
  (3, '2009-12-01', '2009-31-01', 222),
  (3, '2009-12-02', '2009-19-02', 222),
  (3, '2008-14-05', '2006-30-06', 203),
  (3, '2008-19-05', '2006-22-05', 203) ;

DECLARE @DateMax DATE, @DateMin DATE ;
  
SELECT @DateMax = MAX(Ende), 
    @DateMin = MIN(Start) 
FROM  @Sample ;  

WITH 
 Calendar AS 
 (
  SELECT @DateMin AS DateDay
  UNION ALL
  SELECT DATEADD(DAY, 1, DateDay)
  FROM  Calendar
  WHERE DateDay < @DateMax
 )
SELECT S.Typ_ID, 
    S.Art,
    COUNT(DISTINCT C.DateDay)
FROM  Calendar C
    INNER JOIN @Sample S ON C.DateDay BETWEEN S.Start AND S.Ende
GROUP BY S.Typ_ID, S.Art  
OPTION ( MAXRECURSION 0 ) ;

btw, gib DATE(TIME)-Literale immer in der ISO 860 Format an, damit gibt es die geringsten Probleme.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 2

Hallo Stefan,

geht prima mit Deiner Lösung. Danke.

Dauert allerdings im Minutenbereich, bis ein Resultat kommt. Ist es eigentlich für die Performance signifikant, wenn die Hilfetabelle durch CTE gebildet bzw. in die Datenbank geschrieben wird?

Schönen Tag.

Answer 3

hi,

Im Minutenbereich liegts nur, wenn du enstsprechende Datenmengen hast. Das Erstellen der permanente Kalendartaebellle macht schon Sinn, vorallem wenn diese Art der Abfrage öfters vorkommt. Ansonsten brauchst du halt noch entsprechende Indizes auf deine eigentliche Tabelle (Start-/Enddatum). Zut generellen Performance halt zu sagen, das es relative große kartesische Produkte zwischen deinen Intervallen und der Kalendertabelle geben kann. Da ist imho kein Kraut dagegen gewachsen, außer entsprechend frü auf deinen eigentliche Tabelle zu filtern.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann