C11/System/ARI/IQD/Service/SRD_Teile_neu_3.sql

select c157 as "Register No",
	   c156 as "Entry No",
	   c155 as "Srd Item No",
	   c154 as "Dealer No",
	   c153 as "Transaction Type",
	   c132 as "Transaction Type Specification",
	   c152 as "Xtimestamp",
	   c151 as "Current Qty On-hand",
	   c150 as "Open Order Quantity",
	   c149 as "Average Cost",
	   c148 as "Customer Backorder",
	   c147 as "Enable For Requisition",
	   c146 as "Area",
	   c145 as "Document Type",
	   c144 as "Document No",
	   c111 as "Posting Date",
	   c143 as "Quantity",
	   c142 as "Gross Price",
	   c141 as "Unit Price",
	   c140 as "Customer Order No",
	   c139 as "Customer Text",
	   c138 as "Customer No",
	   c137 as "Vin",
	   c136 as "Mileage",
	   c135 as "Salesperson Code",
	   c134 as "Item No",
	   '1' as "Hauptbetrieb",
	   c133 as "Standort",
	   c132 as "BA Spezifikation",
	   c131 as "First Name",
	   c130 as "Last Name",
	   c129 as "Verkäufer",
	   c128 as "No_Customer",
	   c127 as "Name_Customer",
	   c126 as "Customer Group Code",
	   c125 as "Customer Posting Group",
	   c124 as "Location Code_Customer",
	   c123 as "Customer Type",
	   c122 as "Umsatzart",
	   c121 as "Kundenart",
	   c120 as "Kunde",
	   c119 as "Bmw Parts Type",
	   c116 as "Item Group Code",
	   c118 as "Teileart-Gruppe",
	   c117 as "Artikelgruppe-Gruppe",
	   c116 as "Artikelgruppe",
	   c115 as "Teileart",
	   c114 as "Menge",
	   c113 as "VK",
	   c112 as "EK",
	   c111 as "Invoice Date",
	   c110 as "Verkauf / Einkauf",
	   c109 as "Bewegungsart",
	   c95 as "Umsatz_alt",
	   c108 as "Einsatz_alt",
	   c107 as "Bewegungsart Detail",
	   c106 as "Description",
	   c91 as "Betrag Lagerzugang_alt",
	   c93 as "Menge VK_alt",
	   c92 as "Menge Lagerzugang_alt",
	   c105 as "Description_Artikel",
	   c104 as "Teil",
	   c103 as "Abteilung",
	   c102 as "Beleg_ori",
	   c101 as "Beleg",
	   c100 as "Make Code",
	   c99 as "Marke",
	   c98 as "Amount",
	   c97 as "Adjusted Cost",
	   c96 as "Cost Posted To G L",
	   c94 as "Einsatz_neu",
	   COUNT(c155) OVER (partition by c85) as "Anzahl Datensätze",
	   (c95) / (COUNT(c155) OVER (partition by c85)) as "Umsatz",
	   CASE WHEN ((c94) IS NOT NULL) THEN ((c94) / (COUNT(c155) OVER (partition by c85))) ELSE (0) END as "Einsatz",
	   (c93) / (COUNT(c155) OVER (partition by c85)) as "Menge VK",
	   (c92) / (COUNT(c155) OVER (partition by c85)) as "Menge Lagerzugang",
	   (c91) / (COUNT(c155) OVER (partition by c85)) as "Betrag Lagerzugang",
	   c90 as "Extra Code 3",
	   c89 as "Inland/Export",
	   c88 as "Artikelgruppe-Gruppe numerisch",
	   c87 as "4 Stellen Document No",
	   c86 as "Beleg_Tagesbericht"
from 
(select ((cast_numberToString(cast_integer(T1."Entry No_"))) + T1."Document No_") as c85,
	   CASE WHEN ((day((now()) - 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 c86,
	   (left(T1."Document No_",4)) as c87,
	   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 c88,
	   CASE WHEN (T3."Extra Code 3" = 'EXPORT') THEN ('Anteil Export') ELSE ('Anteil Inland') END as c89,
	   T3."Extra Code 3" as c90,
	   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 c91,
	   CASE WHEN ((CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END) = 'Einkauf') THEN (((convert(float, T1."Quantity")))) ELSE (0) END as c92,
	   CASE WHEN ((CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END) = 'Verkauf') THEN (((convert(float, T1."Quantity")))) ELSE (0) END as c93,
	   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 c94,
	   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 c95,
	   (convert(float, T6."Cost Posted to G_L")) as c96,
	   (convert(float, T6."Adjusted Cost")) as c97,
	   (convert(float, T6."Amount")) as c98,
	   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 c99,
	   T4."Make Code" as c100,
	   CASE WHEN ((day((now()) - 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 c101,
	   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 c102,
	   CASE WHEN (T1."Area" = 1) THEN ('T & Z') WHEN (T1."Area" = 4) THEN ('Service') WHEN (T1."Area" = 0) THEN ('Sonstige') ELSE null END as c103,
	   T1."Item No_" + ' - ' + T4."Description" as c104,
	   T4."Description" as c105,
	   T5."Description" as c106,
	   T1."Transaction Type Specification" + ' - ' + T5."Description" as c107,
	   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 c108,
	   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 c109,
	   CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END as c110,
	   T1."Posting Date" as c111,
	   (convert(float, T1."Average Cost")) as c112,
	   (convert(float, T1."Unit Price")) as c113,
	   (convert(float, T1."Quantity")) as c114,
	   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 c115,
	   T4."Item Group Code" as c116,
	   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 c117,
	   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 c118,
	   T4."Parts Category" as c119,
	   CASE WHEN (T3."No_" IS NOT NULL) THEN (T3."No_" + ' - ' + T3."Name") ELSE null END as c120,
	   T3."Gen_ Bus_ Posting Group" as c121,
	   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 c122,
	   T3."Customer Type" as c123,
	   T3."Location Code" as c124,
	   T3."Customer Posting Group" as c125,
	   T3."Customer Group Code" as c126,
	   T3."Name" as c127,
	   T3."No_" as c128,
	   T2."First Name" + ' ' + T2."Last Name" as c129,
	   T2."Last Name" as c130,
	   T2."First Name" as c131,
	   T1."Transaction Type Specification" as c132,
	   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 c133,
	   T1."Item No_" as c134,
	   T1."Salesperson Code" as c135,
	   T1."Mileage" as c136,
	   T1."VIN" as c137,
	   T1."Customer No_" as c138,
	   T1."Customer Text" as c139,
	   T1."Customer Order No_" as c140,
	   T1."Unit Price" as c141,
	   T1."Gross Price" as c142,
	   T1."Quantity" as c143,
	   T1."Document No_" as c144,
	   T1."Document Type" as c145,
	   T1."Area" as c146,
	   T1."Enable for Requisition" as c147,
	   T1."Customer Backorder" as c148,
	   T1."Average Cost" as c149,
	   T1."Open Order Quantity" as c150,
	   T1."Current Qty_ On-Hand" as c151,
	   T1."xTimestamp" as c152,
	   T1."Transaction Type" as c153,
	   T1."Dealer No_" as c154,
	   T1."SRD Item No_" as c155,
	   T1."Entry No_" as c156,
	   T1."Register No_" as c157
from ((((("Vogl7x"."dbo"."BMW AH Vogl$BMW SRD Transaction" T1 left outer join "Vogl7x"."dbo"."BMW AH Vogl$Employee" T2 on T1."Salesperson Code" = T2."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer" T3 on T1."Customer No_" = T3."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Item" T4 on T1."Item No_" = T4."No_") left outer 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 outer 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