MS SQL: In einer Abfrage in Abhängigkeit von XYZ summieren SUM() - Als Query aus VBA heraus

Hallo,

ich brech mir gerade einen ab und hoffe, dass ihr mir helfen könnt.

Ich habe eine Tabelle KHKLagerplatzbestaende.
In dieser Tabelle befinden sich Artikelnummern und Ausprägung, die zusammen den Artikel identifizieren und die PlatzID mit dem jeweiligen Bestand.
Für jede getätigte Zubuchung gibt es eine BestandsID als Primärschlüssel.

Das bedeutet, dass es z.B.

3 Datensätze zum Apfel, rot für die PlatzID 4711 mit den Beständen 10, 20, 30 geben kann und
1 Datensatz zum Apfel, rot für die PlatzID 0815 (Fremdfertiger FF) mit dem Bestand 50

Ich möchte nun in einer Abfrage die Summe für den Gesamtbestand ohne PlatzID 0815 haben sowie den Bestand der PlatzID 0815 ebenfalls angezeigt.

Also in etwa:
SUM(KHKLagerplatzbestaende.Bestand) AS GesamtBestand where PlatzID <> 0815 | = 60 Stück (oder weiterer Ausschlüsse)
SUM(KHKLagerplatzbestaende.Bestand) AS FFBestand where PlatzID = 0815 | = 50 Stück

Meine Abfrage sieht wie folgt aus:

SELECT KHKArtikel.Artikelnummer, KHKArtikel.Bezeichnung1, KHKArtikel.Bezeichnung2, SUM(KHKLagerplatzbestaende.Bestand) AS GesamtBestand, KHKKontokorrent.Matchcode,
KHKVariantenAuspraegungen.Auspraegung1, KHKVariantenAuspraegungen.Auspraegung2, KHKArtikelVarianten.Wiederbeschaffungszeit
FROM KHKArtikel inner JOIN
KHKArtikelVarianten ON KHKArtikel.Artikelnummer = KHKArtikelVarianten.Artikelnummer left JOIN
KHKKontokorrent ON KHKArtikelVarianten.Hauptlieferant = KHKKontokorrent.Kto left JOIN
KHKLagerplatzbestaende ON KHKArtikelVarianten.Artikelnummer = KHKLagerplatzbestaende.Artikelnummer AND
KHKArtikelVarianten.AuspraegungID = KHKLagerplatzbestaende.AuspraegungID left JOIN
KHKVariantenAuspraegungen ON KHKArtikelVarianten.AuspraegungID = KHKVariantenAuspraegungen.AuspraegungID
where KHKArtikel.IstBestellartikel = -1 And KHKArtikel.Aktiv = -1
GROUP BY KHKArtikel.Artikelnummer, KHKArtikel.Bezeichnung1, KHKArtikel.Bezeichnung2, KHKKontokorrent.Matchcode, KHKVariantenAuspraegungen.Auspraegung1, KHKVariantenAuspraegungen.Auspraegung2, KHKArtikelVarianten.Wiederbeschaffungszeit

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 305254

Url: https://administrator.de/forum/ms-sql-in-einer-abfrage-in-abhaengigkeit-von-xyz-summieren-sum-als-query-aus-vba-heraus-305254.html

Ausgedruckt am: 15.04.2025 um 17:04 Uhr

10 Kommentare

Neuester Kommentar

Hi,

und wo genau besteht jetzt dein Problem?

Des Weiteren wäre es eine Hilfe, wenn du dein Query etwas strukturieren würdest (so mache ich es, der Lesbarkeit wegen, zumindest):

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
   KHKArtikel.Artikelnummer
   , KHKArtikel.Bezeichnung1
   , KHKArtikel.Bezeichnung2
   , SUM(KHKLagerplatzbestaende.Bestand) AS GesamtBestand
   , ...
FROM
   KHKArtikel
INNER JOIN xxx on
   xxx = yyy
   and bb = cc
usw.

Gruß
em-pie

Das Problem besteht darin, dass die Summierung für alle PlatzID's außer 0815 erfolgen soll ( AS GesamtBestand) und parallel auch als Summierung für PlatzID 0815 (AS FFBestand).

Irgendwie muss ich doch mit case, if, iif oder sonstwas einschränken wie summiert werden soll. An der Formulierung der Bedinung für GesamtBestand und FFBestand hapert es.

Das Query hab ich nur der Vollständigkeit halber mit reingepackt...

Moin Aximand,

sinngemäß so

1
2
3
4
SELECT KHKArtikel.Artikelnummer, KHKArtikel.Bezeichnung1, KHKArtikel.Bezeichnung2
  , SUM( case when PlatzID<>'0815' then KHKLagerplatzbestaende.Bestand else 0 end) AS GesamtBestand,   
  , SUM( case when PlatzID='0815' then KHKLagerplatzbestaende.Bestand else 0 end) AS FFBestand,   
....

Natürlich ungetestet, und ob PlatzID nun ein String oder etwas nummerisches ist, musst du besser wissen

Grüße
Biber

Hallo Biber,

ja danke - das hab ich nun so auch hinbekommen im Managementstudio.

Jetzt kommt die Gretchenfrage:

Wie kann ich das als Query in VBA benutzen?
'case when' funktioniert dort nicht und es wird auch im Betreff nicht ersichtlich, dass es sich zusätzlich um ein VBA-Problemchen handelt.

Wäre es eine Möglichkeit die funktionierende Abfrage als View anzulegen und dann aus VBA einfach das View anzuzapfen?

Hallo Aximand

Ich mache hiermit eine SQL Abfrage, welche mir das Ergebnis in eine Excel Tabelle schreibt

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
Sub SQL_Abfrage()
Worksheets(1).Activate
'Daten in diesem Tabellenblatt löschen  
Cells.Delete shift:=xlUp
Range("A1").Select  

Dim qt As QueryTable
 
'Eventuell vorhandene SQL-Abfrage in diesem Tabellenblatt löschen  
For Each qt In Worksheets(1).QueryTables
    qt.Delete
Next qt
         
With ActiveSheet.QueryTables.Add(Connection:="OLEDB;Provider=SQLOLEDB.1;" & _  
    "Persist Security Info=True;" & _  
    "User ID=XXXXXXX;" & _  
        "PWD=YYYYYYY;" & _  
        "Data Source=SERVER\SQL_DB;" & _  
        "Use Procedure for Prepare=1;" & _  
        "Auto Translate=True;" & _  
        "Packet Size=4096;" & _  
        "Workstation ID=PC003;" & _  
        "Use Encryption for Data=False;" & _  
        "Tag with column collation when possible=False;" & _  
        "Initial Catalog=SQL_DB", Destination:=Range("A1"))  
        .CommandType = xlCmdSql
        .CommandText = "SELECT KHKArtikel.Artikelnummer, KHKArtikel.Bezeichnung1..."  
        '.Name = "Test"  
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        '.RefreshStyle = xlInsertDeleteCells  
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        '.SourceConnectionFile = _  
        .Refresh BackgroundQuery:=False
    End With
    'Verbindung zu Abfrage trennen, damit Rechtsklick-Kontext richtig funkioniert  
    For Each qt In Worksheets(1).QueryTables
        qt.Delete
    Next qt
End Sub

Gruss

Moin Aximand,

da verstehe ich das Problem nicht.
Das SQL-Statement wird doch auch in VBA als strunzdoofer String transportiert - so wie in Meierjos Beispiel als .CommandText o.ä.

Wenn dort "CASE WHEN" nicht als Teil des Textes, sondern als VBA-Syntax-Element erkannt wird, dann hst du irgendwo eine ungerade Zahl von Anführungszeichen.

Poste doch mal den Brat-mir-den-Text-zusammen.Teil deines Schnipsels.

Grüße
Biber

Hallo Biber,

warum "case when" Fehler verursacht kann ich nicht sagen. Ich bin auch zwischenzeitlich einen anderen Weg gegangen, den ich auch als performanter erachte:

a) View in der Datenbank erstellen:

Create View vwDWDArtikelLieferantenAS

SELECT KHKArtikel.Artikelnummer, KHKArtikel.Bezeichnung1, KHKArtikel.Bezeichnung2, KHKKontokorrent.Matchcode, KHKArtikelVarianten.Dispoformel, KHKArtikelVarianten.Meldebestand,
KHKVariantenAuspraegungen.Auspraegung1, KHKVariantenAuspraegungen.Auspraegung2, KHKArtikelVarianten.Wiederbeschaffungszeit,
SUM(case when KHKLagerplatzbestaende.Lagerkennung = '70137' then KHKLagerplatzbestaende.Bestand else 0 end) AS XYZ,
SUM(case when KHKLagerplatzbestaende.Lagerkennung = '70138' then KHKLagerplatzbestaende.Bestand else 0 end) AS XYZ,
SUM(case when KHKLagerplatzbestaende.Lagerkennung = '70622' then KHKLagerplatzbestaende.Bestand else 0 end) AS XYZ,
SUM(case when KHKLagerplatzbestaende.Lagerkennung = '70861' then KHKLagerplatzbestaende.Bestand else 0 end) AS XYZ,
SUM(case when KHKLagerplatzbestaende.Lagerkennung = '70888' then KHKLagerplatzbestaende.Bestand else 0 end) AS XYZ,
SUM(case when KHKLagerplatzbestaende.Lagerkennung <> '70861' and
KHKLagerplatzbestaende.Lagerkennung <> '70137' and
KHKLagerplatzbestaende.Lagerkennung <> '70138' and
KHKLagerplatzbestaende.Lagerkennung <> '70888' and
KHKLagerplatzbestaende.Lagerkennung <> '70622' and
KHKLagerplatzbestaende.Lagerkennung <> '70861' then KHKLagerplatzbestaende.Bestand else 0 end) AS Gesamtbestand

FROM KHKArtikel inner JOIN
KHKArtikelVarianten ON KHKArtikel.Artikelnummer = KHKArtikelVarianten.Artikelnummer left JOIN
KHKKontokorrent ON KHKArtikelVarianten.Hauptlieferant = KHKKontokorrent.Kto left JOIN
KHKLagerplatzbestaende ON KHKArtikelVarianten.Artikelnummer = KHKLagerplatzbestaende.Artikelnummer AND
KHKArtikelVarianten.AuspraegungID = KHKLagerplatzbestaende.AuspraegungID left JOIN
KHKVariantenAuspraegungen ON KHKArtikelVarianten.AuspraegungID = KHKVariantenAuspraegungen.AuspraegungID
where KHKArtikel.IstBestellartikel = -1 And KHKArtikel.Aktiv = -1
GROUP BY KHKArtikel.Artikelnummer, KHKArtikel.Bezeichnung1, KHKArtikel.Bezeichnung2, KHKKontokorrent.Matchcode, KHKArtikelVarianten.Meldebestand, KHKArtikelVarianten.Dispoformel, KHKVariantenAuspraegungen.Auspraegung1, KHKVariantenAuspraegungen.Auspraegung2, KHKArtikelVarianten.Wiederbeschaffungszeit

b) Abgreifen des VIEWS aus VBA raus

Public Function alleArtikel()
strServer = "ABC"
strDatenbank = "DRAFT"

Set conDB = New ADODB.Connection

conDB.Provider = "SQLOLEDB"
conDB.Properties("Persist Security Info").Value = False
conDB.Properties("Data Source").Value = strServer
conDB.Properties("Initial Catalog").Value = strDatenbank
conDB.Properties("User ID").Value = strAnmeldename
conDB.Properties("Password").Value = strKennwort
conDB.Open

Dim rst As New ADODB.Recordset
Dim strSQLQuery As String
Dim introw As Integer
Dim intcolumn As Integer

' Blatt löschen und aufräumen
Worksheets("Daten").Cells.Clear

strSQLQuery = "Select * from vwDWDArtikelLieferanten" 'das VIEW im SQL-Server abgreifen

rst.ActiveConnection = conDB
rst.Open strSQLQuery

' Initialwerte für Zeile und Spalte auf dem Datenblatt
introw = 2
intcolumn = 3

Daten.Cells(introw - 1, intcolumn) = "Artikelnummer"
Daten.Cells(introw - 1, intcolumn + 1) = "Bezeichnung1"
Daten.Cells(introw - 1, intcolumn + 2) = "Bezeichnung2"
Daten.Cells(introw - 1, intcolumn + 3) = "Ausprägung1"
Daten.Cells(introw - 1, intcolumn + 4) = "Ausprägung2"
Daten.Cells(introw - 1, intcolumn + 5) = "Hauptlieferant"
Daten.Cells(introw - 1, intcolumn + 6) = "Wiederbeschaffungszeit"
Daten.Cells(introw - 1, intcolumn + 7) = "Dispomethode"
Daten.Cells(introw - 1, intcolumn + 8) = "Meldebestand"
Daten.Cells(introw - 1, intcolumn + 9) = "Gesamtbestand"
Daten.Cells(introw - 1, intcolumn + 10) = "XYZ" 'Fremdfertigerlager
Daten.Cells(introw - 1, intcolumn + 11) = "XYZ" 'Fremdfertigerlager
Daten.Cells(introw - 1, intcolumn + 12) = "XYZ" 'Fremdfertigerlager
Daten.Cells(introw - 1, intcolumn + 13) = "XYZ" 'Fremdfertigerlager
Daten.Cells(introw - 1, intcolumn + 14) = "XYZ" 'Fremdfertigerlager

' Da Bestände als money im SQL-Server deklariert sind wegen Genauigkeit
' (4Stellen hinter dem Komma) hier wandeln damit das €-Zeichen verschwindet

Worksheets("Daten").Columns("K").NumberFormat = "0.0000"
Worksheets("Daten").Columns("L").NumberFormat = "0.0000"
Worksheets("Daten").Columns("M").NumberFormat = "0.0000"
Worksheets("Daten").Columns("N").NumberFormat = "0.0000"
Worksheets("Daten").Columns("O").NumberFormat = "0.0000"
Worksheets("Daten").Columns("P").NumberFormat = "0.0000"
Worksheets("Daten").Columns("Q").NumberFormat = "0.0000"

Do Until rst.EOF 'Holen der Daten aus dem recordset
Daten.Cells(introw, intcolumn) = rst.Fields("Artikelnummer").Value
Daten.Cells(introw, intcolumn + 1) = rst.Fields("Bezeichnung1").Value
Daten.Cells(introw, intcolumn + 2) = rst.Fields("Bezeichnung2").Value
Daten.Cells(introw, intcolumn + 3) = rst.Fields("Auspraegung1").Value
Daten.Cells(introw, intcolumn + 4) = rst.Fields("Auspraegung2").Value
Daten.Cells(introw, intcolumn + 5) = rst.Fields("Matchcode").Value
Daten.Cells(introw, intcolumn + 6) = rst.Fields("Wiederbeschaffungszeit").Value
Daten.Cells(introw, intcolumn + 7) = rst.Fields("Dispoformel").Value
Daten.Cells(introw, intcolumn + 8) = rst.Fields("Meldebestand").Value
Daten.Cells(introw, intcolumn + 9) = rst.Fields("Gesamtbestand").Value
Daten.Cells(introw, intcolumn + 10) = rst.Fields("XYZ").Value
Daten.Cells(introw, intcolumn + 11) = rst.Fields("XYZ").Value
Daten.Cells(introw, intcolumn + 12) = rst.Fields("XYZ").Value
Daten.Cells(introw, intcolumn + 13) = rst.Fields("XYZ").Value
Daten.Cells(introw, intcolumn + 14) = rst.Fields("XYZ").Value
introw = introw + 1

rst.MoveNext
Loop
rst.Close
frmAuswahl.Hide 'Eingabemaske schließen
End Function

Wenn ihr Optimierungsvorschläge habt, dann nehm ich die gerne an, sofern ich die verstehe ;)

Moin Aximand,

klar kannst du auch einen View anlegen, wenn es das Handling für dich einfacher macht.
Dennoch wäre es schon interessant, warum das händische Zusammenbasteln des Statements im VBA-Code in die Grütze geht.
Hast du denn auch die einfachen Anführungszeichen (') im String maskiert, also zB als Chr(34) geschrieben?

Anyhow - ändern würde ich noch
a)

1
2
3
4
5
6
7
...
SUM(case when KHKLagerplatzbestaende.Lagerkennung = '70137' then KHKLagerplatzbestaende.Bestand else 0 end) AS XYZ,  
SUM(case when KHKLagerplatzbestaende.Lagerkennung = '70138' then KHKLagerplatzbestaende.Bestand else 0 end) AS XYZ,  
SUM(case when KHKLagerplatzbestaende.Lagerkennung = '70622' then KHKLagerplatzbestaende.Bestand else 0 end) AS XYZ,  
SUM(case when KHKLagerplatzbestaende.Lagerkennung = '70861' then KHKLagerplatzbestaende.Bestand else 0 end) AS XYZ,  
SUM(case when KHKLagerplatzbestaende.Lagerkennung = '70888' then KHKLagerplatzbestaende.Bestand else 0 end) AS XYZ,  
...

Warum willst du denn die DB in Verlegenheit bringen dadurch, dass du 5 Felder gleichermaßen als XYZ benennst?
Meinst du, die DB merkt nicht, dass das ein plumper Test ist, ob sie dir überhaupt richtig zuhört?
Dann lass das "AS XYZ" ganz weg, wenn dir die Namen wurscht sind.

b)

1
2
3
4
5
6
7
....
SUM(case when KHKLagerplatzbestaende.Lagerkennung <> '70861' and  
KHKLagerplatzbestaende.Lagerkennung <> '70137' and  
KHKLagerplatzbestaende.Lagerkennung <> '70138' and  
KHKLagerplatzbestaende.Lagerkennung <> '70888' and  
KHKLagerplatzbestaende.Lagerkennung <> '70622' and  
KHKLagerplatzbestaende.Lagerkennung <> '70861' then KHKLagerplatzbestaende.Bestand else 0 end) AS Gesamtbestand   

besser wäre

1
2
SUM(case when KHKLagerplatzbestaende.Lagerkennung 
    IN ( '70861'  '70137', '70138' , '70888',  '70622'  '70861')  then 0 ELSE KHKLagerplatzbestaende.Bestand end) AS Gesamtbestand   

Noch besser wäre, wenn du mal in die Tabelle ein boolesches Feld einbaust, "Gesamtbestandsrelevant" oder ähnlich.

c) Wenn du zur Ermittlung des Gesamt/FF-Bestands einen JOIN über einen Sack voll Tabellen brauchst, dann könntest du erwägen, bei einem der kommenden Regentage noch mal einen prüfenden Blick auf das Datenmodell zu werfen.
Normalisierung ist ja gut und schön, aber auch kein Selbstzweck.
Eines der Teilziele im täglichen Controller-Leben ist auch, performant und transparent die Ergebnisse abgreifen zu können.
Hat sich bei dem Datenmodell ein Student bei seiner Bachelor-Arbeit verewigt?

Wenn die Laufzeit der Abfrage > 5 sek dauert, dann würde ich den View lieber in einer Tabelle persistieren und täglich/wöchentlich per VBA aktualisieren.
je nachdem, wie oft dieses Resultset gebraucht/angeschaut wird.

Grüße
Bber

Biber - das XYZ steht dort, weil ich nicht die Klartextnamen unserer Fremdfertiger, die sich hinter der Lagerkennung verbergen veröffentlichen will.

zu b) die IN-Möglichkeit kannte ich nicht. Das macht das gnaze etwas kürzer und Wartungsfreundlicher, falls nochmal ein Fremdfertiger hinzu kommt.

c) der Hersteller die Tabellen eben so gestrickt - leider. Es handelt sich um das ERP-System Office Line von sage Software, ehemals ganz früher KHK.