Summierungen und Werte neu setzen, wenn Bedingungen wahr

Romero · 30 März 2022

Hallöchen an das Forum,

ich stehe gerade vor einem Problem und komme nicht weiter, und hoffe, hier geholfen zu werden

Ich möchte aus einer Tabelle, diverse Berechnungen durchführen.
U.a. soll im ersten Schritt eine Summe nach einer Gruppierung erfolgen, als nächstes benötige ich aber die prozentuale Verteilung gegenüber der Gesamtmenge im angegebenen Bereich sowie eine "Wenn-Abfrage" (wenn dies eintritt, dann nehme nur das, ansonsten das).

Meine Ausgangs-Tabelle sieht (eingekürzt) so aus:

Benutzer	Organisations-Bereich	Zeitraum	Abrechnungs-Bereich	abgerechnete Zeit in min
Max	Montage	2022-03-30	Montage	50
Max	Montage	2022-03-30	Montage	40
Max	Montage	2022-03-30	QS	5
Max	Montage	2022-03-30	QS	5
Max	Montage	2022-03-30	QS	3
Max	Montage	2022-03-30	QS	3
Max	Montage	2022-03-30	Versand	200
Max	Montage	2022-03-30	Versand	100
Max	Montage	2022-03-30	Versand	40

Die summierte Tabelle sieht dann entsprechend so aus:

Benutzer	Organisations-Bereich	Zeitraum	Abrechnungs-Bereich	abgerechnete Zeit in min (Summe)
Max	Montage	2022-03-30	Montage	90
Max	Montage	2022-03-30	QS	16
Max	Montage	2022-03-30	Versand	340

Und jetzt kommt die eigentliche Berechnung, wo ich aktuell ne Denk-Blockade habe.
Den %-Anteil der einzelnen summierten Ergebnisse gegenüber der Gesamt-Summe kriege ich noch hin, indem ich die Ausgangs-Tabelle erneut "anzapfe" und mir zum Benutzer und Zeitraum die Summe erstellen lasse:

Benutzer	Organisations-Bereich	Zeitraum	Abrechnungs-Bereich	abgerechnete Zeit in min (Summe)	%-Anteil an Gesamtsumme (Anzahl 446)
Max	Montage	2022-03-30	Montage	90	20,17%
Max	Montage	2022-03-30	QS	16	3,59%
Max	Montage	2022-03-30	Versand	340	76,24%

Am Ende brauche ich aber nur den Wert, welcher blau eingefärbt ist, da dieser >=75% der gesamten Abrechnungs-Zeit ausmacht.
D.h. sein neuer Organisations-Bereich wäre somit "Versand", und somit sähe die summierte Tabelle so aus:

Benutzer	Organisations-Bereich	neuer Org-Bereich	Zeitraum	Abrechnungs-Bereich	abgerechnete Zeit in min (Summe)
Max	Montage	Versand	2022-03-30	Montage	90
Max	Montage	Versand	2022-03-30	QS	16
Max	Montage	Versand	2022-03-30	Versand	340

Sollte keines der Abrechnungs-Bereiche über 75% kommen, dann soll als "neuer Org-Bereich" der Ursprungs-Organisations-Bereich zählen, sprich Montage.

Gern lass ich mir hier eine Hilfstabelle erstellen, welche mir zum Benutzer und Zeitraum den Ur-Organisations-Bereich und den neuen Org-Bereich anzeigt, auf den ich dann mit weiteren Tabellen zugreifen möchte:

Benutzer	Organisations-Bereich	neuer Org-Bereich	Zeitraum
Max	Montage	Versand	2022-03-30

LG Romero

ukulele · 30 März 2022

Code:

WITH tabelle(Benutzer,[Organisations-Bereich],Zeitraum,[Abrechnungs-Bereich],[abgerechnete Zeit in min]) AS (
    SELECT 'Max','Montage','2022-03-30','Montage',50 UNION ALL
    SELECT 'Max','Montage','2022-03-30','Montage',40 UNION ALL
    SELECT 'Max','Montage','2022-03-30','QS',5 UNION ALL
    SELECT 'Max','Montage','2022-03-30','QS',5 UNION ALL
    SELECT 'Max','Montage','2022-03-30','QS',3 UNION ALL
    SELECT 'Max','Montage','2022-03-30','QS',3 UNION ALL
    SELECT 'Max','Montage','2022-03-30','Versand',200 UNION ALL
    SELECT 'Max','Montage','2022-03-30','Versand',100 UNION ALL
    SELECT 'Max','Montage','2022-03-30','Versand',40
    ), t1 AS (
    SELECT    Benutzer,
            [Organisations-Bereich],
            Zeitraum,
            [Abrechnungs-Bereich],
            sum([abgerechnete Zeit in min]) AS sum_Pos
    FROM    tabelle
    GROUP BY Benutzer,
            [Organisations-Bereich],
            Zeitraum,
            [Abrechnungs-Bereich]
    ), t2 AS (
    SELECT    *,
            convert(FLOAT,100) / sum(t1.sum_Pos) OVER (PARTITION BY t1.Benutzer,t1.Zeitraum) * t1.sum_Pos AS sum_Anteil
    FROM    t1
    )
SELECT    t2.*
FROM    t2
WHERE    t2.sum_Anteil >= 75
UNION ALL
SELECT    t3.*
FROM    t2 t3
WHERE    t3.[Organisations-Bereich] = t3.[Abrechnungs-Bereich]
AND NOT EXISTS (    SELECT    1
                    FROM    t2
                    WHERE    t2.sum_Anteil >= 75
                    AND        t3.Benutzer = t2.Benutzer
                    AND        t3.Zeitraum = t2.Zeitraum )

Eventuell noch etwas optimierungsfähig aber sollte erstmal Resultate liefern. Mich stört ein bisschen das NOT EXISTS, das könnte bei großen Datenmengen Laufzeit bedeuten.

Romero · 30 März 2022

Hallo ukulele,

vielen Dank für die schnelle und recht verständliche (Code-)Antwort.
Die Variante mit WITH werd ich mir merken

Ich habe nun versucht, den Code für meine Abfrage anzupassen. Das ging so noch nicht ganz zu 100% auf.
Dazu habe ich noch 2 weitere Frage, für mein End-Ergebnis:

1. Kann ich innerhalb des WITH 2 Tabellen miteinander verbinden? So wie hier?

SQL:

WITH tabelle(Benutzer,[Organisations-Bereich],Zeitraum,[Abrechnungs-Bereich],[abgerechnete Zeit in min]) AS (
    SELECT
            IZ.[Rückmelder] AS [Benutzer],
            IZ.[Org_Bereich] AS [Abrechnungs-Bereich],
            IZ.[Buchungsdatum] AS [Zeitraum],
            IZ.[Durchlaufzeit] AS [abgerechnete Zeit in min],
            SD.[ORGTXK] AS [Organisations-Bereich]
    FROM    [dbo].[Ist_Zeit] AS IZ inner JOIN
            [dbo].[Stammdaten] AS SD ON SD.[BNAME] = UPPER(IZ.[Rückmelder]) AND CONVERT(date, IZ.[Buchungsdatum]) = CONVERT(date, SD.[DATUM])

Oder wir kann ich 2 Tabellen vorab verbinden, ohne dafür eine neue zu erstellen?

2. Meine ausgewählten Felder sind wie folgt konvertiert:
[Rückmelder] = [Benutzer] = varchar
[ORGTXK] = [Organisations-Bereich] = varchar
[Buchungsdatum] = [Zeitraum] = datetime
[Org_Bereich] = [Abrechnungs-Bereich] = varchar
[Durchlaufzeit] = [abgerechnete Zeit in min] = numeric(9,4)

Dennoch bringt er u.a. bei der sum([abgerechnete Zeit in min]) AS sum_Pos den Konvertierungsfehler, dass "Der Operanddatentyp varchar für den sum-Operator ungültig ist", obwohl er hier gar kein varchar angegeben wurde. Bei der Konvertierung in INT, gibt es hier keine Fehlermeldung.

Muss ich hier ggf. die Felder neu Konvertieren lassen?

Und auf deinen Einwand hin, dass es zu Laufzeit kommen könnte, kann ich zumindest soweit eingrenzen (1,5Mio Daten), dass hier die Datenmenge nicht mehr so groß ist. Weitere Abfragen werden dann täglich getätigt und "weggeschrieben".
Daher habe ich in der t1-SELECT-Abfrage ein WHERE über Zeitraum drin, der mir dann nur den letzten Tag anzeigt.

LG Romero

ukulele · 30 März 2022

zu 1.)
Also im WITH-Teil kannst du auch ganz normal joinen etc.. Der Code

Code:

WITH tabelle(Benutzer,[Organisations-Bereich],Zeitraum,[Abrechnungs-Bereich],[abgerechnete Zeit in min]) AS (
    SELECT
            IZ.[Rückmelder] AS [Benutzer],
            IZ.[Org_Bereich] AS [Abrechnungs-Bereich],
            IZ.[Buchungsdatum] AS [Zeitraum],
            IZ.[Durchlaufzeit] AS [abgerechnete Zeit in min],
            SD.[ORGTXK] AS [Organisations-Bereich]
    FROM    [dbo].[Ist_Zeit] AS IZ inner JOIN
            [dbo].[Stammdaten] AS SD ON SD.[BNAME] = UPPER(IZ.[Rückmelder]) AND CONVERT(date, IZ.[Buchungsdatum]) = CONVERT(date, SD.[DATUM])
) SELECT * FROM tabelle

ist auch syntaktisch richtig. Hast du eventuell noch die abschließende Klammer aus dem WITH-Teil vergessen?

Wenn der Code wie gezeigt nicht läuft, dann läuft auch der SELECT im WITH nicht, also nur

Code:

SELECT
            IZ.[Rückmelder] AS [Benutzer],
            IZ.[Org_Bereich] AS [Abrechnungs-Bereich],
            IZ.[Buchungsdatum] AS [Zeitraum],
            IZ.[Durchlaufzeit] AS [abgerechnete Zeit in min],
            SD.[ORGTXK] AS [Organisations-Bereich]
    FROM    [dbo].[Ist_Zeit] AS IZ inner JOIN
            [dbo].[Stammdaten] AS SD ON SD.[BNAME] = UPPER(IZ.[Rückmelder]) AND CONVERT(date, IZ.[Buchungsdatum]) = CONVERT(date, SD.[DATUM])

zu 2.)
Durchlaufzeit ist der echte Spaltenname oder? Und das Format ist ist sicher NUMERIC(9,4)?

Dann sollte sich ein sum(IZ.Durchlaufzeit) berechnen lassen, solange die Summe der Werte nicht tatsächlich das Format sprengt (wenn du z.B. alle Werte in der Tabelle summierst). Bei Aggregaten müssen natürlich alle Spalten entweder aggregiert oder gruppiert werden, also besser du zeigst mal den ganzen Code der den Fehler schmeisst.

Romero · 30 März 2022

Hallo ukulele,

der Fehler saß vorm Rechner. Einige Konvertierungen waren nicht ausreichend. Somit kam es zu den Konvertierungsfehlern, und das führte den Script nicht richtig aus.
Ich habe es angepasst (neue Tabelle mit entsprechenden Formatierungen erstellt) und hier klappt es wunderbar.
Habe deinen Code meiner Abfrage angepasst.

Noch ne klitzekleine Frage hätte ich noch. Könnte man zu der Gesamt-Summierung (siehe untere Tabelle) den neuen Org_Bereich hinzufügen?
Also nicht das Separieren auf die eine Zeile.

Benutzer	Organisations-Bereich	neuer Org-Bereich	Zeitraum	Abrechnungs-Bereich	abgerechnete Zeit in min (Summe)
Max	Montage	Versand	2022-03-30	Montage	90
Max	Montage	Versand	2022-03-30	QS	16
Max	Montage	Versand	2022-03-30	Versand	340

LG Romero

ukulele · 30 März 2022

Ach jetzt checke ich erst was du mit "neuem Organisations-Bereich" meinst, vor allem das du das nicht als eigenen Datensatz sondern vermutlich in der Spalte brachst. Damit könnte man auch das unliebsame NOT EXISTS umgehen, ich hab das mal umgestellt:

Code:

WITH tabelle(Benutzer,[Organisations-Bereich],Zeitraum,[Abrechnungs-Bereich],[abgerechnete Zeit in min]) AS (
    SELECT 'Max','Montage','2022-03-30','Montage',50 UNION ALL
    SELECT 'Max','Montage','2022-03-30','Montage',40 UNION ALL
    SELECT 'Max','Montage','2022-03-30','QS',5 UNION ALL
    SELECT 'Max','Montage','2022-03-30','QS',5 UNION ALL
    SELECT 'Max','Montage','2022-03-30','QS',3 UNION ALL
    SELECT 'Max','Montage','2022-03-30','QS',3 UNION ALL
    SELECT 'Max','Montage','2022-03-30','Versand',200 UNION ALL
    SELECT 'Max','Montage','2022-03-30','Versand',100 UNION ALL
    SELECT 'Max','Montage','2022-03-30','Versand',40
    ), t1 AS (
    SELECT    Benutzer,
            [Organisations-Bereich],
            Zeitraum,
            [Abrechnungs-Bereich],
            sum([abgerechnete Zeit in min]) AS sum_Pos
    FROM    tabelle
    GROUP BY Benutzer,
            [Organisations-Bereich],
            Zeitraum,
            [Abrechnungs-Bereich]
    ), t2 AS (
    SELECT    *,
            convert(FLOAT,100) / sum(t1.sum_Pos) OVER (PARTITION BY t1.Benutzer,t1.Zeitraum) * t1.sum_Pos AS sum_Anteil
    FROM    t1
    )
SELECT    t1.Benutzer,
        isnull(t2.[Organisations-Bereich],t1.[Organisations-Bereich]) AS [Organisations-Bereich],
        t1.Zeitraum,
        t1.[Abrechnungs-Bereich],
        t1.sum_Pos
FROM    t1
LEFT JOIN t2
ON        t1.Benutzer = t2.Benutzer
AND        t1.[Organisations-Bereich] = t2.[Organisations-Bereich]
AND        t1.Zeitraum = t2.Zeitraum
AND        t2.sum_Anteil >= 75

Nur wenn die Schwelle für einen neuen Organisations-Bereich unter 50% fällt gibts dann ein Problem weil dann theoretisch mehrere Bereiche in Frage kommen können.

Romero · 30 März 2022

Ich werde es morgen testen und dich darüber informieren

Bei <= 50% soll der Ursprungs-Bereich [Organisations-Bereich] genommen werden.

Morgen dazu mehr...

Bis dahin und vielen lieben Dank bis hierher...

Romero · 31 März 2022

Hallo ukulele,

So: ich habe dein Code genommen, angepasst und getestet.
Und muss sagen: es passt wunderbar

Also dir nochmal vielen vielen lieben Dank.

Ich habe hier noch als Bedingung angegeben
case when t2.sum_Anteil >= 75 then isnull(t2.[Organisations-Bereich],t1.[Organisations-Bereich])
else t1.[Organisations-Bereich]
end AS [Organisations-Bereich]

Und somit erhalte ich alles was unter 75% ist, den Ur-Organisations-Bereich, alles was größer ist als 75% dann den neuen Organisations-Bereich.

LG Romero

ukulele · 31 März 2022

Romero schrieb:
Ich habe hier noch als Bedingung angegeben
case when t2.sum_Anteil >= 75 then isnull(t2.[Organisations-Bereich],t1.[Organisations-Bereich])
else t1.[Organisations-Bereich]
end AS [Organisations-Bereich]

Braucht es eigentlich nicht. Durch den LEFT JOIN werden nur Daten gejoint, wenn 75% überschritten wurde. Wenn also irgendeine Spalte aus t2 nicht NULL ist, dann ist >75% = true. Daher kann man statt einem CASE auch einfach isnull(t2.[Organisations-Bereich],t1.[Organisations-Bereich]) nutzen, liefert entweder den ursprünglichen Bereich (wenn >75%) oder den Neuen. Dein CASE ist also redundant.

Summierungen und Werte neu setzen, wenn Bedingungen wahr

Romero

Aktiver Benutzer

ukulele

Datenbank-Guru

Romero

Aktiver Benutzer

ukulele

Datenbank-Guru

Romero

Aktiver Benutzer

ukulele

Datenbank-Guru

Romero

Aktiver Benutzer

Romero

Aktiver Benutzer

ukulele

Datenbank-Guru