SQL: Anzahl der Monate aus Zeiträumen ermitteln

[Papaschlumpf73]

Hallo Gemeinde,

aus einer Tabelle mit Leistungszeiträumen für verschiedene Kunden muss ich die Anzahl der Monate ermitteln, in denen ein Kunde Leistungen bezogen hat. Jeder Kunde kann pro Monat mehrere Leistungen beziehen, so dass durchaus auch mehr als 100000 Leistungszeiträume für einen Kunden vorhanden sein können. Jeder Leistungszeitraum beginnt am 1. eines Monats und endet am letzten Tag eines Monats; Teilmonate sind also nicht möglich. Das ganze sollte möglichst direkt durch ein SQL-Statement (ohne Delphi) ermittelt werden können.

Beispiel:

KDNR;BezugVon;BezugBis
123;01.01.2020;31.01.2020
123;01.03.2020;30.04.2020
123;01.04.2020;31.05.2020

Anzahl der Monate mit Leistungsbezug = 4 (Januar, März, April und Mai 2020)

Mein erster Ansatz war jetzt, eine temporäre Tabelle mit Monat und Jahr zur erstellen, jeden Monat einzutragen und am Ende zu gruppieren. Die Anzahl der gruppierten Datensätze entspräche dann der Anzahl der Monate.

Hat jemand eine Idee, wie man z.B. aus dem Zeitraum 01.03.2020 bis 30.04.2020 möglichst performant die 2 Zeilen (März+April) in eine temporäre Tabelle bekommt? Oder hat jemand ggf. noch eine viel bessere Idee, um die Monate zu zählen?

[Delphi.Narium]

Nur hingedaddelt als Idee:

SQL-Code:

select KdNr, sum(Monate) as AnzahlMonate from 
(
  select  KdNr, BezugVon, BezugBis, 
    case when DateDiff(d, BezugVon, BezugBis) > 30 
         then DateDiff(d, BezugVon, BezugBis) / 30.0 
         else 0 
    end as Monate 
  from Tabelle
) Monatsabfrage
group by KdNr

Idee von dort https://stackoverflow.com/questions/...-in-sql-server abgekupfert.

[Jumpy]

Mach dir eine Hilfstabelle mit dem Monatsersten für alle Monate des Jahres (ggf. mehrerer Jahre). Dann ein Select auf diese Tabelle (ggf. eingeschränkt auf einen Zeitraum, z.B. wenn du nur die 1. Jahreshälft betrachten willst). Dazu Joinst du die Leistungszeiträume eines Kunden, die dann in den jeweiligen Monat fallen und schließt die Monate aus, für die sich keine Leistungen finden. Dann gruppier das Ganze nur noch nach Monat und du kannst die Monate zählen.

So eine hilfstabelle kann man bei vielen DBs auch mit "with" erzeugen:

SQL-Code:

-- Oracle
with Monatstabelle as (
  Select Trunc(Current_Date,'Year') As Monat From Dual Union
  Select Add_Months(Trunc(Current_Date,'Year')+1) As Monat From Dual Union
  Select Add_Months(Trunc(Current_Date,'Year')+2) As Monat From Dual Union
  ...
  Select Add_Months(Trunc(Current_Date,'Year')+11) As Monat From Dual
  )

Select count(Distinct Monat) as Anzahl
From Monatstabelle M
Left Join Zeitraumtabelle Z
  ON M.Monat Between Z.Bezug_Von and Z.Bezug_Bis
  and KDNR='1234'
Where KDNR is not NULL

[Papaschlumpf73]

Zitat:

Zitat von Delphi.Narium (Beitrag 1477619)

Nur hingedaddelt als Idee:

SQL-Code:

select KdNr, sum(Monate) as AnzahlMonate from 
(
  select  KdNr, BezugVon, BezugBis, 
    case when DateDiff(d, BezugVon, BezugBis) > 30 
         then DateDiff(d, BezugVon, BezugBis) / 30.0 
         else 0 
    end as Monate 
  from Tabelle
) Monatsabfrage
group by KdNr

Idee von dort https://stackoverflow.com/questions/...-in-sql-server abgekupfert.

Das wäre zu einfach... Zeiträume, die sich überschneiden, werden dabei leider doppelt und dreifach gezählt.

[Papaschlumpf73]

Zitat:

Zitat von Jumpy (Beitrag 1477624)

Mach dir eine Hilfstabelle mit dem Monatsersten für alle Monate des Jahres (ggf. mehrerer Jahre). Dann ein Select auf diese Tabelle (ggf. eingeschränkt auf einen Zeitraum, z.B. wenn du nur die 1. Jahreshälft betrachten willst). Dazu Joinst du die Leistungszeiträume eines Kunden, die dann in den jeweiligen Monat fallen und schließt die Monate aus, für die sich keine Leistungen finden. Dann gruppier das Ganze nur noch nach Monat und du kannst die Monate zählen.

Danke, das klingt vielversprechend. Aus Performancegründen werde ich wahrscheinlich die Hilfstabelle nicht temporär sondern dauerhaft erzeugen und noch mit einem Index versehen. Von 1900 - 2020 sind das nur 1440 Monate.