SELECT "Register No", "Entry No", "Srd Item No", "Dealer No", "Transaction Type", "Transaction Type Specification", "Xtimestamp", "Current Qty On-hand", "Open Order Quantity", "Average Cost", "Customer Backorder", "Enable For Requisition", "Area", "Document Type", "Document No", "Posting Date", "Quantity", "Gross Price", "Unit Price", "Customer Order No", "Customer Text", "Customer No", "Vin", "Mileage", "Salesperson Code", "Item No", '1' AS "Hauptbetrieb", "Standort", "Transaction Type Specification" AS "BA Spezifikation", "First Name", "Last Name", "Verk�ufer", "No_Customer", "Name_Customer", "Customer Group Code", "Customer Posting Group", "Location Code_Customer", "Customer Type", "Umsatzart", "Kundenart", "Kunde", "Bmw Parts Type", "Item Group Code", "Teileart-Gruppe", "Artikelgruppe-Gruppe", "Item Group Code" AS "Artikelgruppe", "Teileart", "Menge", "VK", "EK", "Posting Date" AS "Invoice Date", "Verkauf / Einkauf", "Bewegungsart", "Umsatz_alt", "Einsatz_alt", "Bewegungsart Detail", "Description", "Betrag Lagerzugang_alt", "Menge VK_alt", "Menge Lagerzugang_alt", "Description_Artikel", "Teil", "Abteilung", "Beleg_ori", "Beleg", "Make Code", "Marke", "Amount", "Adjusted Cost", "Cost Posted To G L", "Einsatz_neu", COUNT("Srd Item No") OVER (PARTITION BY c85) AS "Anzahl Datens�tze", ("Umsatz_alt") / (COUNT("Srd Item No") OVER (PARTITION BY c85)) AS "Umsatz", CASE WHEN (("Einsatz_neu") IS NOT NULL) THEN (("Einsatz_neu") / (COUNT("Srd Item No") OVER (PARTITION BY c85))) ELSE (0) END AS "Einsatz", ("Menge VK_alt") / (COUNT("Srd Item No") OVER (PARTITION BY c85)) AS "Menge VK", ("Menge Lagerzugang_alt") / (COUNT("Srd Item No") OVER (PARTITION BY c85)) AS "Menge Lagerzugang", ("Betrag Lagerzugang_alt") / (COUNT("Srd Item No") OVER (PARTITION BY c85)) AS "Betrag Lagerzugang", "Extra Code 3", "Inland/Export", "Artikelgruppe-Gruppe numerisch", "4 Stellen Document No", "Beleg_Tagesbericht" FROM ( SELECT ((((T1."Entry No_"))) + T1."Document No_") AS c85, CASE WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 3) THEN ( ( CASE WHEN (T3."No_" IS NOT NULL) THEN ( T1."Document No_" + ' / ' + ( CASE WHEN (T3."No_" IS NOT NULL) THEN (T3."No_" + ' - ' + T3."Name") ELSE NULL END ) ) ELSE (T1."Document No_") END ) ) ELSE NULL END AS "Beleg_Tagesbericht", (left(T1."Document No_", 4)) AS "4 Stellen Document No", CASE WHEN (T4."Item Group Code" BETWEEN '00' AND '22') THEN ('Artikelgruppe 0 - 22') WHEN (T4."Item Group Code" BETWEEN '23' AND '33') THEN ('Artikelgruppe 23 - 33') WHEN (T4."Item Group Code" BETWEEN '34' AND '99') THEN ('Artikelgruppe 34 - 99') ELSE ('Teileart fehlt/Rest') END AS "Artikelgruppe-Gruppe numerisch", CASE WHEN (T3."Extra Code 3" = 'EXPORT') THEN ('Anteil Export') ELSE ('Anteil Inland') END AS "Inland/Export", T3."Extra Code 3" AS "Extra Code 3", CASE WHEN ( ( CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END ) = 'Einkauf' ) THEN (((convert(FLOAT, T1."Quantity"))) * ((convert(FLOAT, T1."Unit Price")))) ELSE (0) END AS "Betrag Lagerzugang_alt", CASE WHEN ( ( CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END ) = 'Einkauf' ) THEN (((convert(FLOAT, T1."Quantity")))) ELSE (0) END AS "Menge Lagerzugang_alt", CASE WHEN ( ( CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END ) = 'Verkauf' ) THEN (((convert(FLOAT, T1."Quantity")))) ELSE (0) END AS "Menge VK_alt", CASE WHEN ( ( ( CASE WHEN ( ( CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END ) = 'Verkauf' ) THEN (((convert(FLOAT, T1."Quantity"))) * ((convert(FLOAT, T1."Average Cost")))) ELSE (0) END ) < 0 ) AND (((convert(FLOAT, T6."Adjusted Cost"))) > 0) ) THEN (((convert(FLOAT, T6."Adjusted Cost"))) * - 1) WHEN ( ( ( CASE WHEN ( ( CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END ) = 'Verkauf' ) THEN (((convert(FLOAT, T1."Quantity"))) * ((convert(FLOAT, T1."Average Cost")))) ELSE (0) END ) > 0 ) AND (((convert(FLOAT, T6."Adjusted Cost"))) < 0) ) THEN (((convert(FLOAT, T6."Adjusted Cost"))) * - 1) ELSE (((convert(FLOAT, T6."Adjusted Cost")))) END AS "Einsatz_neu", CASE WHEN ( ( CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END ) = 'Verkauf' ) THEN (((convert(FLOAT, T1."Quantity"))) * ((convert(FLOAT, T1."Unit Price")))) ELSE (0) END AS "Umsatz_alt", (convert(FLOAT, T6."Cost Posted to G_L")) AS "Cost Posted To G L", (convert(FLOAT, T6."Adjusted Cost")) AS "Adjusted Cost", (convert(FLOAT, T6."Amount")) AS "Amount", CASE WHEN (T4."Make Code" IN ('BMW', 'BMW-C1', 'BMW-MOT', 'BMWI')) THEN ('BMW') WHEN (T4."Make Code" IN ('BMW-MINI')) THEN ('MINI') ELSE ('Andere') END AS "Marke", T4."Make Code" AS "Make Code", CASE WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 180) THEN ( ( CASE WHEN (T3."No_" IS NOT NULL) THEN ( T1."Document No_" + ' / ' + ( CASE WHEN (T3."No_" IS NOT NULL) THEN (T3."No_" + ' - ' + T3."Name") ELSE NULL END ) ) ELSE (T1."Document No_") END ) ) ELSE NULL END AS "Beleg", CASE WHEN (T3."No_" IS NOT NULL) THEN ( T1."Document No_" + ' / ' + ( CASE WHEN (T3."No_" IS NOT NULL) THEN (T3."No_" + ' - ' + T3."Name") ELSE NULL END ) ) ELSE (T1."Document No_") END AS "Beleg_ori", CASE WHEN (T1."Area" = 1) THEN ('T & Z') WHEN (T1."Area" = 4) THEN ('Service') WHEN (T1."Area" = 0) THEN ('Sonstige') ELSE NULL END AS "Abteilung", T1."Item No_" + ' - ' + T4."Description" AS "Teil", T4."Description" AS "Description_Artikel", T5."Description" AS "Description", T1."Transaction Type Specification" + ' - ' + T5."Description" AS "Bewegungsart Detail", CASE WHEN ( ( CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END ) = 'Verkauf' ) THEN (((convert(FLOAT, T1."Quantity"))) * ((convert(FLOAT, T1."Average Cost")))) ELSE (0) END AS "Einsatz_alt", CASE WHEN (T1."Transaction Type" = 'BA') THEN ('BA - Bestellung') WHEN (T1."Transaction Type" = 'RA') THEN ('RA - R�ckgaben') WHEN (T1."Transaction Type" = 'LB') THEN ('LB - Lagerzug�nge maschinell') WHEN (T1."Transaction Type" = 'LC') THEN ('LC - Lagerabg�nge') WHEN (T1."Transaction Type" = 'LA') THEN ('LA - Lagerzug�nge manuell') ELSE NULL END AS "Bewegungsart", CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END AS "Verkauf / Einkauf", T1."Posting Date" AS "Posting Date", (convert(FLOAT, T1."Average Cost")) AS "EK", (convert(FLOAT, T1."Unit Price")) AS "VK", (convert(FLOAT, T1."Quantity")) AS "Menge", CASE WHEN (T4."Parts Category" = '1') THEN ('1 - Teile') WHEN (T4."Parts Category" = '2') THEN ('2 - Tauschteile') WHEN (T4."Parts Category" = '3') THEN ('3 - Nachr�stteile') WHEN (T4."Parts Category" = '4') THEN ('4 - ') WHEN (T4."Parts Category" = '5') THEN ('5 - R�der, Felgen') WHEN (T4."Parts Category" = '6') THEN ('6 - ') WHEN (T4."Parts Category" = '7') THEN ('7 - Accessoires') WHEN (T4."Parts Category" = '8') THEN ('8 - Reifen') WHEN (T4."Parts Category" = '9') THEN ('9 - �le, Sonstiges') ELSE NULL END AS "Teileart", T4."Item Group Code" AS "Item Group Code", CASE WHEN (T4."Item Group Code" IN ('B', 'C', 'A')) THEN ('Artikelgruppe A - C') WHEN (T4."Item Group Code" IN ('E', 'K', 'H', 'F', 'I', 'G', 'D', 'J')) THEN ('Artikelgruppe D - K') WHEN (T4."Item Group Code" BETWEEN '23' AND '33') THEN ('Artikelgruppe D + E neu') WHEN (T4."Item Group Code" BETWEEN '34' AND '99') THEN ('Artikelgruppe F - K neu') ELSE ('Teileart fehlt/Rest') END AS "Artikelgruppe-Gruppe", CASE WHEN (T4."Parts Category" IN ('1', '2')) THEN ('Teileart 1 - 2') WHEN (T4."Parts Category" IN ('3', '4', '5', '6', '7', '8', '9')) THEN ('Teileart 3 - 9') ELSE ('Teileart fehlt') END AS "Teileart-Gruppe", T4."Parts Category" AS "Bmw Parts Type", CASE WHEN (T3."No_" IS NOT NULL) THEN (T3."No_" + ' - ' + T3."Name") ELSE NULL END AS "Kunde", T3."Gen_ Bus_ Posting Group" AS "Kundenart", CASE WHEN (T3."No_" LIKE 'I%') THEN ('Intern') WHEN (T3."No_" LIKE 'G%') THEN ('GWL') WHEN ( ( (NOT T3."No_" LIKE 'I%') AND (NOT T3."No_" LIKE 'G%') ) AND (T3."No_" IS NOT NULL) ) THEN ('Extern') ELSE NULL END AS "Umsatzart", T3."Customer Type" AS "Customer Type", T3."Location Code" AS "Location Code_Customer", T3."Customer Posting Group" AS "Customer Posting Group", T3."Customer Group Code" AS "Customer Group Code", T3."Name" AS "Name_Customer", T3."No_" AS "No_Customer", T2."First Name" + ' ' + T2."Last Name" AS "Verk�ufer", T2."Last Name" AS "Last Name", T2."First Name" AS "First Name", T1."Transaction Type Specification" AS "Transaction Type Specification", CASE WHEN (T1."Dealer No_" IN ('00357', '29682', '22075', '28303')) THEN ('10') WHEN (T1."Dealer No_" IN ('00557', '29619', '40119')) THEN ('20') ELSE NULL END AS "Standort", T1."Item No_" AS "Item No", T1."Salesperson Code" AS "Salesperson Code", T1."Mileage" AS "Mileage", T1."VIN" AS "Vin", T1."Customer No_" AS "Customer No", T1."Customer Text" AS "Customer Text", T1."Customer Order No_" AS "Customer Order No", T1."Unit Price" AS "Unit Price", T1."Gross Price" AS "Gross Price", T1."Quantity" AS "Quantity", T1."Document No_" AS "Document No", T1."Document Type" AS "Document Type", T1."Area" AS "Area", T1."Enable for Requisition" AS "Enable For Requisition", T1."Customer Backorder" AS "Customer Backorder", T1."Average Cost" AS "Average Cost", T1."Open Order Quantity" AS "Open Order Quantity", T1."Current Qty_ On-Hand" AS "Current Qty On-hand", T1."xTimestamp" AS "Xtimestamp", T1."Transaction Type" AS "Transaction Type", T1."Dealer No_" AS "Dealer No", T1."SRD Item No_" AS "Srd Item No", T1."Entry No_" AS "Entry No", T1."Register No_" AS "Register No" FROM ( ( ( ( ( "Vogl7x"."dbo"."BMW AH Vogl$BMW SRD Transaction" T1 LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$Employee" T2 ON T1."Salesperson Code" = T2."No_" ) LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$Customer" T3 ON T1."Customer No_" = T3."No_" ) LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$Item" T4 ON T1."Item No_" = T4."No_" ) LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$BMW SRD Movement Type" T5 ON (T1."Transaction Type" = T5."Movement Type Code") AND (T1."Transaction Type Specification" = T5."Movement Type Specification") ) LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$Value Entry" T6 ON ( (T6."Item No_" = T1."SRD Item No_") AND (T6."Document No_" = T1."Document No_") ) AND (T6."Posting Date" = T1."Posting Date") ) WHERE ( (T1."Posting Date" >= convert(DATETIME, '2020-01-01 00:00:00.000')) AND (NOT T1."Document No_" LIKE 'FILAG%') ) ) D1 -- order by "Xtimestamp" asc