none
Komplexe ineinander geschachtelte Abfrage RRS feed

  • Frage

  • SELECT 	
        'KUNDE' as Kundenkategorie, 
    	c.i_customer_m AS CustId,
    	c.s_CustNo AS CustNo,
    	c.s_Name1 AS CustName1, 
    	c.s_name2 AS CustName2,
    	c.S_STREET AS CustStreet,
    	c.S_ZIPCODENO as CustZipCodeNo,
    	c.S_TOWN as CustTown
    
    FROM
    	sao.invoice_p i WITH (nolock), sao.customer_M c WITH (nolock)
    WHERE 
    	i.i_customer_m=c.i_customer_m  AND 
    	c.i_customer_m > 0 AND 
    	i.i_invoice_p > 0 AND 
    	i.dt_deleted IS NULL AND
    	i.b_cancel = 0 AND  
        i.b_proforma = 0 AND 
    	i.I_CUSTOMER_M <> 0 AND 
        DATEDIFF(mm,i.D_INVOICEDATE,getdate()) <= 60
    GROUP BY
    
        c.i_customer_m,
    	c.s_custno, 
    	c.s_name1,
    	c.s_name2,
    	c.S_STREET,
    	c.S_ZIPCODENO,
    	c.S_TOWN
    
    UNION ALL 
    
    SELECT 
    
        'INTERESSENT' as Kundenkategorie, 
        c.i_customer_m AS CustId,
    	c.s_custno AS CustNo, 
    	c.s_name1 AS CustName1,
    	c.s_name2 AS CustName2,
    	c.S_STREET AS CustStreet,
    	c.S_ZIPCODENO as CustZipCodeNo,
    	c.S_TOWN as CustTown
    	
    
    
    FROM
    	sao.customer_M c  WITH (nolock)
    WHERE 
    	c.i_customer_m NOT in (
    	select c2.I_CUSTOMER_M 
    	FROM
    	sao.invoice_p i2 WITH (nolock), sao.customer_M c2 WITH (nolock) --Vergleich Kundennummer das sie in Invoice nicht vorhanden ist
    	
    WHERE 
    	i2.i_customer_m=c2.i_customer_m  AND 
    	c2.i_customer_m >0 AND 
    	i2.i_invoice_p>0 AND 
    	i2.dt_deleted IS NULL AND
    	i2.b_cancel = 0 AND  
        i2.b_proforma = 0 AND  
        DATEDIFF(mm,i2.D_INVOICEDATE,getdate()) <= 60	) --letzten 5 Jahre
    	AND c.I_CUSTOMER_M <> 0 
    
    UNION ALL
    
    SELECT
    	'Neukunde' as Kundengategorie,
    	c.i_customer_m AS CustId,
    	c.s_custno AS CustNo, 
    	c.s_name1 AS CustName1,
    	c.s_name2 AS CustName2,
    	c.S_STREET AS CustStreet,
    	c.S_ZIPCODENO as CustZipCodeNo,
    	c.S_TOWN as CustTown
    FROM sao.CUSTOMER_M c WITH (Nolock)
    WHERE 
    	c.i_customer_m IN (
    	select c3.I_CUSTOMER_M 
    	FROM
    	sao.invoice_p i3 WITH (nolock), sao.customer_M c3 WITH (nolock)
    	
    WHERE 
    	i3.i_customer_m=c3.i_customer_m  AND 
    	c3.i_customer_m >0 AND 
    	i3.i_invoice_p>0 AND 
    	i3.dt_deleted IS NULL AND
    	i3.b_cancel = 0 AND  
        i3.b_proforma = 0 AND  
    	i3.D_INVOICEDATE BETWEEN  MONTH (getdate()) +3  AND MONTH (getdate()) +24 
    	AND
        DATEDIFF(mm,i3.D_INVOICEDATE,getdate()) <= 60	)
    	AND c.I_CUSTOMER_M <> 0  
    
    	UNION ALL 
    
    SELECT 
    
        'Verlorener Kunde' as Kundenkategorie, 
        c.i_customer_m AS CustId,
    	c.s_custno AS CustNo, 
    	c.s_name1 AS CustName1,
    	c.s_name2 AS CustName2,
    	c.S_STREET AS CustStreet,
    	c.S_ZIPCODENO as CustZipCodeNo,
    	c.S_TOWN as CustTown
    	
    
    
    FROM
    	sao.customer_M c  WITH (nolock)
    WHERE 
    	c.i_customer_m  in (
    	select c4.I_CUSTOMER_M 
    	FROM
    	sao.invoice_p i4 WITH (nolock), sao.customer_M c4 WITH (nolock) 
    	
    WHERE 
    	i4.i_customer_m=c4.i_customer_m  AND 
    	c4.i_customer_m >0 AND 
    	i4.i_invoice_p>0 AND 
    	i4.dt_deleted IS NULL AND
    	i4.b_cancel = 0 AND  
        i4.b_proforma = 0 AND  
    	i4.D_INVOICEDATE BETWEEN MONTH (getdate()) +24  AND MONTH (getdate()) +60  --letzten 5 Jahre
    	AND DATEDIFF(mm,i4.D_INVOICEDATE,getdate()) <= 60	)
    	AND c.I_CUSTOMER_M <> 0 

    Hallo,

    also erstmal, ich bin noch ein Anfänger was SQL angeht, und bräuchte daher Hilfe bei einer Abfrage. Die Abfrage selber funktioniert. Nur leider sind die Ergebnisse nicht ganz so sehr wie erhofft. Das Ziel der Abfrage ist es den Kunden in unterschiedliche Kategorien zu sortieren, abhängig von seinen letzten Rechnungsdatum.

    Vielleicht kann mir hier ja jemand weiterhelfen und mein Verständnis für SQL erweitern.

    Freitag, 23. April 2021 09:56

Alle Antworten

  • Warum fragst du ein Feld vom Typ DATUM auf einem Monatswert vom Typ ZAHL ab?
    Außer dem fragst du mit "heute + 12 Monate bis heute + 60 Monate" in der Zukunft ab;-).

    i4.D_INVOICEDATE BETWEEN MONTH (getdate()) +24  AND MONTH (getdate()) +60 

    Korrekt wäre da eher

    i4.D_INVOICEDATE between DATEADD(MONTH, -60, getdate()) and GETDATE()

    Die Sortierung kannst du danach mit

    order by Kundengategorie, D_INVOICEDATE

    Das Feld D_INVOICEDATE sollte auch in deiner Ergebnisliste vorhanden sein.



    Freitag, 23. April 2021 10:10
  • Hallo und vielen Dank für die schnelle Antwort.
    Hatte da wohl dann doch einen kleinen Denkfehler drin :)

    Die Abfrage für die "Verlorenen" funktioniert jetzt super. Aber überschreibt es irgendwie die Abfrage von i3? Die habe ich jetzt auch so weit abgeändert.
    SELECT
    	'Neukunde' as Kundengategorie,
    	c.i_customer_m AS CustId,
    	c.s_custno AS CustNo, 
    	c.s_name1 AS CustName1,
    	c.s_name2 AS CustName2,
    	c.S_STREET AS CustStreet,
    	c.S_ZIPCODENO as CustZipCodeNo,
    	c.S_TOWN as CustTown
    FROM sao.CUSTOMER_M c WITH (Nolock)
    WHERE 
    	c.i_customer_m IN (
    	select c3.I_CUSTOMER_M 
    	FROM
    	sao.invoice_p i3 WITH (nolock), sao.customer_M c3 WITH (nolock)
    	
    WHERE 
    	i3.i_customer_m=c3.i_customer_m  AND 
    	c3.i_customer_m >0 AND 
    	i3.i_invoice_p>0 AND 
    	i3.dt_deleted IS NULL AND
    	i3.b_cancel = 0 AND  
        i3.b_proforma = 0 AND  
    	i3.D_INVOICEDATE BETWEEN   DATEADD(Month, -3, getdate()) AND DATEADD(Month, -24, getdate()) 
    	AND
        DATEDIFF(mm,i3.D_INVOICEDATE,getdate()) <= 60	)
    	AND c.I_CUSTOMER_M <> 0  

    Freitag, 23. April 2021 10:23
  • Die Unterabfrage von i3 prüft ja nur das Vorhandensein von Kunden mit dem Rechnungsdatum.
    Es liefert dir die Rechnungsinfo ja nicht.

    Dazu bedarf es dann anderer Logik:

    select c.*, i3.D_INVOICEDATE from ( select ... union all select ... ) c left join sao.invoice_p i3 on <Beziehhung>

    where ....


    Freitag, 23. April 2021 11:07
  • Hmm irgendwie erschließt es sich mir noch nicht ganz. Ich lasse mir das noch einmal über Nacht durch den Kopf gehen, da ich das Gefühl habe das du es eigentlich verständlich erklärst und ich gerade einfach die Logik nicht richtig erfassen kann.
    Aber vielen lieben Dank für die Hilfe. Das gibt mir denke die richtigen Denkansätze für das Ganze, mache SQL jetzt wirklich erst seit 2 Wochen und habe noch starke Probleme mit dem JOIN / UNION Befehlen und deren Logik dahinter.
    Ich wünsche dir ein schönes Wochenende :)

    Freitag, 23. April 2021 12:02
  • Guten Abend Nimala,

    mir sind neben den bereits angesprochenen Berechnungen der Datumswerte 2 weitere Dinge aufgefallen, die die Abfrage unübersichtlich machen, und zudem den Wartungsaufwand bei Anpassungen deutlich erhöhen.

    Zum einen hast Du einen Kern, der in allen Abfragen bzw. in hierin enthaltenen Unterabfragen identisch verwendet wird. Es ist sehr viel übersichtlicher, diesen in CTE's auszulagern, die Vorberechnungen ausführen.

    Ferner stellst Du die Beziehungen zwischen Tabellen in der WHERE Klausel dar. Dies ist zum einen nicht günstig, weil man damit schwer erkennen kann, was echte Bedingungen sind. Zum anderen ist dies dann immer ein INNER JOIN, und Du nimmt Dir damit die Möglichkeit, einen LEFT JOIN oder RIGHT JOIN zu verwenden.

    Ich habe Deine Abfrage diesen Ausführungen folgend nur anders formuliert (mit der Möglichkeit, dass hier vielleicht doch noch ein Anpassungsfehler enthalten ist, weil ich es nicht testen kann). Die Logik sollte aber trotzdem klar werden hoffe ich.

    Schönen Abend.

    --------------------------------------------------------------
    /* Vorbereitung der Abfragen durch CTE's */
    --------------------------------------------------------------
    
    /* wiederkehrend in allen Abfragen */
    WITH Basisabfrage AS (
            SELECT c.i_customer_m AS CustId,
    	    c.s_custno AS CustNo, 
    	    c.s_name1 AS CustName1,
    	    c.s_name2 AS CustName2,
    	    c.S_STREET AS CustStreet,
    	    c.S_ZIPCODENO as CustZipCodeNo,
    	    c.S_TOWN as CustTown
        FROM
    	    sao.customer_M c
    )
    /* wiederkehrend in allen Unterabfragen mit ... where xyz IN (SELECT ... bzw where xyz NOT IN (SELECT ... */
    , Hauptunterabfrage AS (
        select c.I_CUSTOMER_M,
            i.D_INVOICEDATE
        FROM sao.invoice_p i 
            LEFT JOIN sao.customer_M c ON (i.i_customer_m = c.i_customer_m) --Vergleich Kundennummer das sie in Invoice nicht vorhanden ist
        WHERE c.i_customer_m >0  
    	    AND i.i_invoice_p>0  
    	    AND i.dt_deleted IS NULL 
    	    AND i.b_cancel = 0   
            AND i.b_proforma = 0  
    )
    , Unterabfrage_2 AS (
        select I_CUSTOMER_M
        from Hauptunterabfrage 
        where DATEDIFF(mm,D_INVOICEDATE,getdate()) <= 60	) --letzten 5 Jahre
    )
    , Unterabfrage_3 AS (
        select I_CUSTOMER_M
        from Hauptunterabfrage 
    	where D_INVOICEDATE BETWEEN  MONTH (getdate()) + 3  AND MONTH (getdate()) + 24 
    	    AND DATEDIFF(mm, D_INVOICEDATE,getdate()) <= 60	)
    )
    , Unterabfrage_4 AS (
        select I_CUSTOMER_M
        from Hauptunterabfrage 
        where D_INVOICEDATE BETWEEN MONTH (getdate()) + 24  AND MONTH (getdate()) + 60  --letzten 5 Jahre
    	    AND DATEDIFF(mm, D_INVOICEDATE,getdate()) <= 60	)
    )
    
    --------------------------------------------------------------
    /* Verwendung der vorbereiteten Abfragen */
    --------------------------------------------------------------
    SELECT 'KUNDE' as Kundenkategorie, *
    FROM Basisabfrage b
        LEFT JOIN sao.invoice_p i ON (i.i_customer_m = b.CustId)
    WHERE b.CustId > 0  
    	AND i.i_invoice_p > 0  
    	AND i.dt_deleted IS NULL 
    	AND i.b_cancel = 0   
        AND i.b_proforma = 0  
    	AND i.I_CUSTOMER_M <> 0  
        AND  DATEDIFF(mm,i.D_INVOICEDATE,getdate()) <= 60
    
    UNION ALL
    
    SELECT 'INTERESSENT' as Kundenkategorie, *
    FROM Basisabfrage 
    WHERE CustId NOT in (SELECT I_CUSTOMER_M FROM Unterabfrage_2)
        AND CustId <> 0 
    
    UNION ALL
    
    SELECT 'Neukunde' as Kundengategorie, *
    FROM Basisabfrage b
        LEFT JOIN sao.invoice_p i ON (i.i_customer_m = b.CustId)
    WHERE i_customer_m IN IN (I_CUSTOMER_M FROM Unterabfrage_3)
        AND CustId <> 0
    
    UNION ALL
    
    SELECT 'Verlorener Kunde' as Kundenkategorie, *
    FROM Basisabfrage
    WHERE i_customer_m  in (SELECT I_CUSTOMER_M FROM Unterabfrage_4)
        AND CustId <> 0

       




    Freitag, 23. April 2021 17:51
  • Guten Tag, 

    vielen Dank für die Mühe und die ausführliche Antwort mit Erklärung. Nun kam ich heute dazu die Abfrage zu testen und da hatte er mir zuerst ein Fehler gegeben bezüglich des UNION Befehles da nicht gleich viele Ausdrücke in den Ziellisten vorhanden sind. Da ich bei meiner Schulung gelernt hatte das CTE's immer nur für den Select Befehl danach funktioniert dachte ich das ich die Unterabfragen einfach in der jeweiligen FROM Klausel zu ergänzen. Dies hat dazu geführt das der Befehl zwar nun ausgeführt wird aber die Rechnungen erhalten alle mehrere Daten wo zuvor eigentlich nur ein Datum stand. Ich bin mir nicht ganz sicher woher genau dies jetzt zustande kommt und hoffe Sie können mir hierbei noch helfen und mein Verständnis stärken. Anbei habe ich dann auch nochmal den leicht geänderten Code. Ich hoffe Sie können mir weiter helfen. 

    --------------------------------------------------------------
    /* Vorbereitung der Abfragen durch CTE's */
    --------------------------------------------------------------
    
    /* wiederkehrend in allen Abfragen */
    WITH Basisabfrage AS (
            SELECT c.i_customer_m AS CustId,
    	    c.s_custno AS CustNo, 
    	    c.s_name1 AS CustName1,
    	    c.s_name2 AS CustName2,
    	    c.S_STREET AS CustStreet,
    	    c.S_ZIPCODENO as CustZipCodeNo,
    	    c.S_TOWN as CustTown,
    		i.D_INVOICEDATE as Rechnungsdatum
        FROM
    	    sao.customer_M c, sao.invoice_p i
    )
    /* wiederkehrend in allen Unterabfragen mit ... where xyz IN (SELECT ... bzw where xyz NOT IN (SELECT ... */
    , Hauptunterabfrage AS (
        select c.I_CUSTOMER_M,
            i.D_INVOICEDATE
        FROM sao.invoice_p i 
            LEFT JOIN sao.customer_M c ON (i.i_customer_m = c.i_customer_m) --Vergleich Kundennummer das sie in Invoice nicht vorhanden ist
        WHERE c.i_customer_m >0  
    	    AND i.i_invoice_p>0  
    	    AND i.dt_deleted IS NULL 
    	    AND i.b_cancel = 0   
            AND i.b_proforma = 0  
    )
    
    --------------------------------------------------------------
    /* Verwendung der vorbereiteten Abfragen */
    --------------------------------------------------------------
    SELECT 'KUNDE' as Kundenkategorie, *
    FROM Basisabfrage b
        LEFT JOIN sao.invoice_p i ON (i.i_customer_m = b.CustId)
    WHERE b.CustId > 0  
    	AND i.i_invoice_p > 0  
    	AND i.dt_deleted IS NULL 
    	AND i.b_cancel = 0   
        AND i.b_proforma = 0  
    	AND i.I_CUSTOMER_M <> 0  
        AND  DATEDIFF(mm,i.D_INVOICEDATE,getdate()) <= 60
    
    UNION
    
    SELECT 'INTERESSENT' as Kundenkategorie, *
    FROM Basisabfrage b
    	LEFT JOIN sao.invoice_p i ON (i.i_customer_m = b.CustId)
    WHERE CustId NOT in (SELECT  I_CUSTOMER_M
        from Hauptunterabfrage 
        where DATEDIFF(mm,D_INVOICEDATE,getdate()) <= 60)
        AND CustId <> 0 
    
    UNION 
    
    SELECT 'Neukunde' as Kundengategorie, *
    FROM Basisabfrage b
        LEFT JOIN sao.invoice_p i ON (i.i_customer_m = b.CustId)
    WHERE i_customer_m IN  (SELECT I_CUSTOMER_M
        from Hauptunterabfrage 
    	where D_INVOICEDATE BETWEEN  MONTH (getdate()) - 3  AND MONTH (getdate()) - 24 
    	    AND DATEDIFF(mm, D_INVOICEDATE,getdate()) <= 60	)
        AND CustId <> 0
    
    UNION 
    
    SELECT 'Verlorener Kunde' as Kundenkategorie, *
    FROM Basisabfrage b
    	LEFT JOIN sao.invoice_p i ON  (i.i_customer_m = b.CustId) 
    WHERE i_customer_m  in (SELECT I_CUSTOMER_M  I_CUSTOMER_M
        from Hauptunterabfrage 
        where D_INVOICEDATE BETWEEN MONTH (getdate()) - 24  AND MONTH (getdate()) - 60  --letzten 5 Jahre
    	    AND DATEDIFF(mm, D_INVOICEDATE,getdate()) <= 60	)
        AND CustId <> 0



    • Bearbeitet Nimala Mittwoch, 28. April 2021 10:03
    Mittwoch, 28. April 2021 10:02
  • Das Problem ist, dass du deine Joins in die Unions mit reinpackst.

    with .....
    
    select * from (
      select ....
      from basisabfrage
      union all
      select ....
      from basisabfrage
      :
    ) basis
    left join ......
    where .....

    Union alleine schließt doppelte Zeilen aus, also "union distinct"-

    "Union All" nimmt alle Zeilen, eine Distinctprüfung erfolgt nicht.

    Mittwoch, 28. April 2021 11:15
  • Guten Abend,

    das "nicht gleich viele Ausdrücke in den Ziellisten vorhanden sind" lag daran, dass ich bei den Abfragen mit den UNIONS im Select mit einem * gearbeitet habe. Da aber in einigen Abfragen ein JOIN verwendet wird, sind die Ausgabespalten nicht mehr übereinstimmend. 

    Besser wäre es da also, statt * neu b.* zu verwenden. Damit sollte es passen.

    Ohne die Tabellenstruktur und Daten wird es schwer Dir zu beantworten, warum die Datensätze doppelt sind.

    Die Technik um der Ursache auf die Spur zu kommen, wäre die Abfragen nach:

    /* Verwendung der vorbereiteten Abfragen */

    auszukommentieren, und dann dann die einzelnen Abfragen jeweils einzeln auszuführen, ob das Ergebnis stimmt, also beginnend mit SELECT * FROM basisabfrage, danach die weiteren Abfragen, und bei den UNIONS später auch immer nur eine Abfrage ausführen.

    Wenn Du trotzdem nicht weiterkommst, wäre es notwendig, die Tabellenstruktur mit Beispieldaten hier zur Verfügung zu stellen.

    Schönen Abend.

    Donnerstag, 29. April 2021 18:43
  • Spätestens, wenn ein Join keine 1:1 sondern eine 1:N-Beziehung hat, werden die Daten der Haupttabelle vervielfältigt.
    Freitag, 30. April 2021 05:57