Zeiträume nach Bedingungen zusammenfassen

Question

Guten Tag,

ich habe (vereinfacht) folgendes Tabelle:

USE tempdb
GO
IF OBJECT_ID(N'dbo.Buchungen', 'U') IS NOT NULL
    DROP TABLE dbo.Buchungen
GO
CREATE TABLE dbo.Buchungen (
    id_person int NOT NULL,
    monat datetime NOT NULL,
    betrag INT)
INSERT INTO dbo.Buchungen (id_person, monat, betrag)
SELECT 1,			'01.01.2007', 15.00
UNION ALL SELECT 1, '01.02.2007', 30.00
UNION ALL SELECT 1, '01.03.2007', 25.00
UNION ALL SELECT 1, '01.08.2007', 10.00
UNION ALL SELECT 1, '01.09.2007', 15.00
UNION ALL SELECT 2, '01.02.2008', 20.00
UNION ALL SELECT 2, '01.03.2008', 15.00
UNION ALL SELECT 2, '01.05.2008', 30.00
UNION ALL SELECT 2, '01.09.2008', 70.00
UNION ALL SELECT 2, '01.12.2008', 90.00

Die Ergebnisse sollen (je Person) zusammengefasst werden, soweit der Zeitraum kleiner 3 Monate ist.

Das Ergebnis soll also so aussehen:

Möglich ist das bestimmt, und Ihr habt wahrscheinlich wie immer gute Ideen, die mir dazu fehlen.

Danke schon einmal.

Answer 1

Hallo Joerg,

abgesehen davon, daß das Ergebnis für Id_person=2 ab 1.2. nicht ganz zur Anforderung "<3 Monate" paßt, hier mein Ansatz:

;
WITH  cte
AS 
( 
	SELECT
		b1.*,
		x.monat AS monat_grp,
		DATEDIFF(mm, x.monat, b1.monat) / 3 AS grp
	FROM Buchungen b1
	CROSS APPLY 
	( 
		SELECT TOP 1 monat
		FROM Buchungen b2
		WHERE
			b2.id_person = b1.id_person
		ORDER BY monat
	) x
)
SELECT
	id_person,
	CONVERT(CHAR(10),MIN(monat),104) AS Beginn,
	CONVERT(CHAR(10),MAX(monat),104) AS Ende,
	DATEDIFF(mm, MIN(monat), MAX(monat)) + 1 AS Diff_Monate,
	SUM(Betrag) AS Betrag
FROM cte
GROUP BY
	id_person,
	grp
ORDER BY
	id_person,
	grp

Statt einer Ergebniszeile mit DIFF_Monate=4 gibt es zwei Zeilen mit Diff_Monate=2 bzw. 1. Das paßt auch eher zur Gesamtanzahl der Beispieldatensätze.

Falls ich etwas falsch verstanden haben sollte, bitte ich um Präzisierung.

MfG, Lutz

Answer 2

Guten Abend Lutz,

das sieht wirklich schon sehr gut aus.

Ich habe statt 3 Monate auch einmal auf 7 Monate umgestellt; dabei gibt es dies Result:

Nun gibt es bei Person_1 keine Differenz (Unterbrechnung) von mehr als 7 Monaten zum Vorgänger; das bedeutet hier soll nicht gruppiert werden, und das Ergebnis müsste so angezeigt werden:

Beginn (01.01.2007)  Ende (01.09.2007)  DIFF_Monate (9)  Betrag (95)

Liegt das nun an meiner nicht so präzisen Beschreibung, die ich gegeben habe, oder daran, dass man dort gar nicht auf 7 ändern darf?

Wäre nett wenn Du noch mal schaust.

Schönen Abend.

Answer 3

In Deinem ersten Post hast Du von einem "Zeitraum kleiner 3 Monate" gesprochen. In dem letzten Post geht es um eine "Unterbrechung von mehr als x Monaten".

Beides lösbar, aber unterschiedliche Logik... Sofern sich bis morgen niemand anderes meldet, schau ich mir das Thema noch mal an. Sollte aber lösbar sein.

Answer 4

Guten Morgen, Lutz,

ich wollte es damit nicht komplizierter machen als es ohnehin schon ist.

Bei etwas geänderter Ausgangsdatenmenge ist das aber auch bei 3 Monaten reproduzierbar:

USE tempdb
GO
IF OBJECT_ID(N'dbo.Buchungen', 'U') IS NOT NULL
    DROP TABLE dbo.Buchungen
GO
CREATE TABLE dbo.Buchungen (
    id_person int NOT NULL,
    monat datetime NOT NULL,
    betrag INT)
INSERT INTO dbo.Buchungen (id_person, monat, betrag)
SELECT 1,			'01.01.2007', 15.00
UNION ALL SELECT 1, '01.02.2007', 30.00
UNION ALL SELECT 1, '01.03.2007', 25.00
UNION ALL SELECT 1, '01.04.2007', 10.00
UNION ALL SELECT 1, '01.09.2007', 15.00
UNION ALL SELECT 2, '01.02.2008', 20.00
UNION ALL SELECT 2, '01.03.2008', 15.00
UNION ALL SELECT 2, '01.05.2008', 30.00
UNION ALL SELECT 2, '01.09.2008', 70.00
UNION ALL SELECT 2, '01.12.2008', 90.00

Weil aber zwischen dem 01.03.2007 und 01.04.2007 keine Differenz von mehr als 3 Monaten war, müssten die beiden gelb hinterlegten Datensätze ebenfalls zusammengefasst werden (also so lange zusammenfassen, bis der nächstfolgende Datensatz bei der Person_ID grösser 3 Monate):

id_person   Beginn            Ende               Diff_       Monate Betrag
1                 01.01.2007    01.04.2007     4            80

Ich hoffe ich habe es jetzt besser erläutert. Sonst frage bitte noch einmal, bevor Du Dir viel Mühe für eine Lösung machst.

Schöne Sonntagsgrüsse.

Answer 5

Der Code ist jetzt doch etwas komplexer geworden... (Kommentare im Quellcode. Bei Rückfragen bitte melden.)

DECLARE @range INT = 3; -- Zeitraum kleiner "@range" Monate

WITH cteBuchungen AS	-- Buchungen je id_person nummerieren
(
	SELECT 
		id_person,
		Monat,
		ROW_NUMBER() OVER (PARTITION BY id_person ORDER BY Monat) pos
	FROM Buchungen
)
,cteGrp AS				-- Differenz zum "nächsten Eintrag" finden, dabei Range berücksichtigen 
(
	SELECT 
		ISNULL(b1.id_person,b2.id_person)id_person ,
		ISNULL(b1.Monat,b2.Monat-1) Monat,
		ISNULL(DATEDIFF(mm,b1.Monat,b2.Monat) / @range , 1) AS grp
	FROM cteBuchungen b1
	FULL OUTER JOIN cteBuchungen b2
	ON b1.id_person = b2.id_person AND b1.pos = b2.pos - 1
), 
cteSub AS				-- Monate nummerieren, die der Beginn für einen Bereich sind
(
	SELECT  
		id_person,
		Monat,
		grp,
		ROW_NUMBER() OVER(PARTITION BY id_person ORDER BY Monat ) pos2
	FROM cteGrp
	WHERE grp > 0 
), 
cteRange as				-- Bereiche ermitteln (von ... bis)
(
	SELECT 
		c1.id_person,
		c1.Monat AS von, 
		c2.Monat AS bis
	FROM cteSub c1
	INNER JOIN cteSub c2
		ON c1.id_person=c2.id_person AND c1.pos2=c2.pos2-1
)
-- und zum Schluß selektieren und aggregieren
SELECT  
	b.id_person,
	CONVERT(CHAR(10),MIN(b.Monat),104) AS Beginn, 
	CONVERT(CHAR(10),MAX(b.Monat),104) AS Ende, 
	DATEDIFF(mm,MIN(b.Monat), MAX(b.Monat)) + 1 AS Diff_Monate, 
	SUM(b.betrag) AS Betrag
FROM    Buchungen b
INNER JOIN cteRange r
	ON b.id_person=r.id_person
	AND b.Monat > r.von
	AND b.Monat <= r.bis
GROUP BY b.id_person, r.bis;

Falls das Ganze zu Performanceproblemen führt, müßte das Ganze evtl. über temp Tabellen aufgeteilt werden.

Answer 6

Hallo Lutz,

etwas habe ich es noch geändert:

DECLARE @range INT = 3; -- Zeitraum kleiner "@range" Monate
WITH cteBuchungen AS	-- Buchungen je id_person nummerieren
(
	SELECT 
		id_person,
		Monat,
		ROW_NUMBER() OVER (PARTITION BY id_person ORDER BY Monat) pos
	FROM Buchungen
)
,cteGrp AS				-- Differenz zum "nächsten Eintrag" finden, dabei Range berücksichtigen 
(
	SELECT 
		ISNULL(b1.id_person,b2.id_person)id_person ,
		ISNULL(b1.Monat,b2.Monat -1) Monat,
		(CASE WHEN ( (DATEDIFF(mm,b1.Monat,b2.Monat)) > @range
		             OR b1.pos IS NULL
		             OR b2.pos IS NULL )
		  THEN 0
		  ELSE 1
		END) AS grp
	FROM cteBuchungen b1
	FULL OUTER JOIN cteBuchungen b2
	ON b1.id_person = b2.id_person AND b1.pos = b2.pos - 1
), 
cteSub AS				-- Monate nummerieren, die der Beginn für einen Bereich sind
(
	SELECT  
		id_person,
		Monat,
		grp,
		ROW_NUMBER() OVER(PARTITION BY id_person ORDER BY Monat ) pos2
	FROM cteGrp
	WHERE grp = 0 
), 
cteRange as				-- Bereiche ermitteln (von ... bis)
(
	SELECT 
		c1.id_person,
		c1.Monat AS von, 
		c2.Monat AS bis
	FROM cteSub c1
	INNER JOIN cteSub c2
		ON c1.id_person=c2.id_person AND c1.pos2=c2.pos2-1
)
 -- und zum Schluß selektieren und aggregieren
SELECT  
	b.id_person,
	CONVERT(CHAR(10),MIN(b.Monat),104) AS Beginn, 
	CONVERT(CHAR(10),MAX(b.Monat),104) AS Ende, 
	DATEDIFF(mm,MIN(b.Monat), MAX(b.Monat)) + 1 AS Diff_Monate, 
	SUM(b.betrag) AS Betrag
FROM    Buchungen b
INNER JOIN cteRange r
	ON b.id_person=r.id_person
	AND b.Monat > r.von
	AND b.Monat <= r.bis
GROUP BY b.id_person, r.bis;

Das sieht nur sehr gut aus. Vielen herzlichen Dank für Deine Unterstützung.

Jörg