Gültigkeitsdauer auf Einzelwerte ausschlüsseln

Question

Hallo,

ich habe folgendes Problem:

wir sollen Datensätze (in diesem Fall Kontrakte) mit Gültigkeiten (von - bis als Datetime) auslesen. Dies ist der erste Schritt und nicht wirklich schwer.

Allerdings sollen jetzt für jeden Gültigkeitsmonat die entsprechenden Teilmengen ausgewiesen werden und hier kommen wir momentan nur weiter wenn wir mit Schleifen arbeiten.

Ausgangssituation(Beispiel1):

	Gültig von	Gültig bis	Anzahl Monate	Gesamtmenge	Menge je Monat
Kontrakt A	01.02.2016	30.09.2016	8	640000	80000

Dieses soll nun in folgendes Format gebracht werden(Beispiel2):

	Jahr	Monat	Menge
Kontrakt A	2016	2	80000
Kontrakt A	2016	3	80000
Kontrakt A	2016	4	80000
Kontrakt A	2016	5	80000
Kontrakt A	2016	6	80000
Kontrakt A	2016	7	80000
Kontrakt A	2016	8	80000
Kontrakt A	2016	9	80000

Ich möchte ungern mit Schleifen arbeiten, da es sicherlich eine Funktion gibt die so etwas kann.

Danke schon mal im Vorraus

Ralf 

---

Ralf S,

Answer 1

Hallo Ralf,

kann man das nicht mir GROUP BY erledigen? Wo fehlt es da?

Mit den Funktionen MIN, MAX, SUM, COUNT kann man sich die Werte ableiten. "Menge je Monat" ist nicht klar genug definiert. Ist das ein Durchschnitt? dann AVG.

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com

Answer 2

Hallo Andreas,

die Gesamtsumme eines Kontraktes soll über die Gesamte Laufzeit (in Monaten) Gleichverteilt werden.

Was uns fehlt ist der einfachste Weg von Beispiel 1 zu Beispiel 2.

Das Beispiel1 ist die Grundlage und Beispiel2 das Ziel. Klar könnte ich jetzt anfangen eine Schleife zu bauen, aber dabei bleibt die Performance natürlich auf der Strecke, da es sich um mehrere Tausend Kontrakte handeln kann und dann noch jeweils die Einzelnen Monate je Kontrakt zu ermitteln sind.

Ich hoffe es wird jetzt klarer und bin für jede Hilfe dankbar.

Gruß Ralf

---

Ralf S,

Answer 3

Ach so herum, das Wort "Ausgangssituation" hatte ich geflissentlich überlesen.

Also im Prinzip benötigst Du ein "UNGROUP"

Dafür kann man mit CTEs arbeiten, aber eigentlich reicht auch eine sogenannte "Numbers-Table", die Du dann über "Anzahl Monate" mit dem "<" oder "<=" -Operator Joinen kannst, und bis zum Maximalwert (hier "8") Zeilen erzeugst, die du dann mit den Werten auffüllst. Das sollte ziemlich straight-forward gehen, da Du die "Menge je Monat" ja bereits fertig hast, und dort nichts weiter kalkulieren muss.

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com

Answer 4

Hallo Ralf,

so könntest Du es lösen:

SET DATEFORMAT ymd;

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

CREATE TABLE #temp_kontrakte(
kontrakt varchar (20),
von DATETIME,
bis DATETIME,
Monatsmenge INT
);
GO 

INSERT INTO #temp_kontrakte VALUES ('Kontakt_A', '2016-02-01','2016-09-30', 8000);

-- MinMax Kontraktdaten für Datum-Hilfstabelle ermitteln
DECLARE @min_von AS DATETIME;
DECLARE @max_bis AS DATETIME;
SELECT @min_von = MIN (von) FROM #temp_Kontrakte;
SELECT @max_bis = MAX(bis) FROM #temp_Kontrakte;


-- Tabelle mit Monaten füllen
WITH CTE_Datumstabelle
AS
(
  SELECT @min_Von AS [datum]
  UNION ALL
  SELECT DATEADD(mm, 1, [datum])
  FROM CTE_Datumstabelle
  WHERE [datum] < DATEADD(mm, -1, @max_bis)
)

SELECT YEAR (datum) AS Jahr, 
	MONTH (datum) AS Monat,
	Monatsmenge
FROM CTE_Datumstabelle d
INNER JOIN #temp_kontrakte k ON (d.Datum BETWEEN k.von AND k.bis)
OPTION (MAXRECURSION 0);
GO

Ggfs. musst Du noch gültig (von) und gültig (bis) aus Deinem Beispiel in DATETIME-Werte umwandeln.

Schöne Grüße.

Answer 5

Super Hinweis, Danke. Auf die Idee mit der Zusatztabelle bin ich nicht gekommen, Danke!!

Gruß

Ralf

---

Ralf S,