C11/System/NAVISION/IQD/Serv_Teile/werkstattverbrauch_neu.sql

select "Document Type_2" as "Document Type_2",
	   "No" as "No",
	   "Sell-to Customer No" as "Sell-to Customer No",
	   "Bill-to Customer No" as "Bill-to Customer No",
	   "Bill-to Name" as "Bill-to Name",
	   "Bill-to Name 2" as "Bill-to Name 2",
	   "Order Date" as "Order Date",
	   "Posting Date_2" as "Posting Date_2",
	   "Posting Description" as "Posting Description",
	   "Location Code_2" as "Location Code_2",
	   "Department Code_2" as "Department Code_2",
	   "Make Code" as "Make Code",
	   "Customer Posting Group" as "Customer Posting Group",
	   "Salesperson Code" as "Salesperson Code",
	   "Order Class" as "Order Class",
	   "Sell-to Customer Name" as "Sell-to Customer Name",
	   "Sell-to Customer Name 2" as "Sell-to Customer Name 2",
	   "Document Date" as "Document Date",
	   "No Series" as "No Series",
	   "Posting No Series" as "Posting No Series",
	   "Shipping No Series" as "Shipping No Series",
	   "Date Archived" as "Date Archived",
	   "Archived By User" as "Archived By User",
	   "Reason For Archiving" as "Reason For Archiving",
	   "Service Posting Group" as "Service Posting Group",
	   "Vin" as "Vin",
	   "License No" as "License No",
	   "Mileage" as "Mileage",
	   "Model Code" as "Model Code",
	   "Model No" as "Model No",
	   "Prod Year" as "Prod Year",
	   "Description 2" as "Description 2",
	   "Initial Registration" as "Initial Registration",
	   "Gen Prod Posting Group_2" as "Gen Prod Posting Group_2",
	   "No Of Cylinders" as "No Of Cylinders",
	   "Cylinder Capacity (ccm)" as "Cylinder Capacity (ccm)",
	   "Power (kw)" as "Power (kw)",
	   "Power (hp)" as "Power (hp)",
	   "Book No" as "Book No",
	   "Branch Book No" as "Branch Book No",
	   "Sales Department Code_2" as "Sales Department Code_2",
	   "Item Sales Pr Grp Int Chrg" as "Item Sales Pr Grp Int Chrg",
	   "Int Charg Labor Price Group" as "Int Charg Labor Price Group",
	   "Ext Serv Int Charging Pr Grp" as "Ext Serv Int Charging Pr Grp",
	   "Post With Book No" as "Post With Book No",
	   "Internal Order" as "Internal Order",
	   "Branch Code" as "Branch Code",
	   "Work Completed" as "Work Completed",
	   "Sales Branch Code_falsch" as "Sales Branch Code_falsch",
	   "Vehicle Status" as "Vehicle Status",
	   "Entry No" as "Entry No",
	   "Order No" as "Order No",
	   "Posting Date" as "Posting Date",
	   "Document No" as "Document No",
	   "Type" as "Type",
	   "No_Product" as "No_Product",
	   "Description_Product" as "Description_Product",
	   "Quantity" as "Quantity",
	   "Direct Unit Cost" as "Direct Unit Cost",
	   "Unit Cost" as "Unit Cost",
	   "Total Cost" as "Total Cost",
	   "Unit Price" as "Unit Price",
	   "Total Price" as "Total Price",
	   "Location Code" as "Location Code",
	   "Department Code" as "Department Code",
	   "Work Type Code" as "Work Type Code",
	   "User Id" as "User Id",
	   "Amt To Post To G L" as "Amt To Post To G L",
	   "Amt Posted To G L" as "Amt Posted To G L",
	   "Entry Type" as "Entry Type",
	   "Positive" as "Positive",
	   "Quantity (base)" as "Quantity (base)",
	   "Time Type" as "Time Type",
	   "Service Job No" as "Service Job No",
	   "Sales Department Code" as "Sales Department Code",
	   "Qty Per Hour" as "Qty Per Hour",
	   "Qty (hour)" as "Qty (hour)",
	   "Document Type" as "Document Type",
	   "Customer Group Code" as "Customer Group Code",
	   "Standard Time" as "Standard Time",
	   "Standard Time Type" as "Standard Time Type",
	   "Order Line No" as "Order Line No",
	   "Order Completed" as "Order Completed",
	   "Sales Branch Code" as "Sales Branch Code",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort_Department" as "Standort_Department",
	   "Code_Salesperson" as "Code_Salesperson",
	   "Name_Salesperson" as "Name_Salesperson",
	   "Serviceberater_falsch" as "Serviceberater_falsch",
	   "Menge" as "Menge",
	   "Einstandsbetrag" as "Einstandsbetrag",
	   "Verkaufsbetrag" as "Verkaufsbetrag",
	   "Gen Bus Posting Group" as "Gen Bus Posting Group",
	   "Gen Prod Posting Group" as "Gen Prod Posting Group",
	   "Source Type" as "Source Type",
	   "Source No" as "Source No",
	   "Source No (payment)" as "Source No (payment)",
	   "Umsatzart" as "Umsatzart",
	   "Marke" as "Marke",
	   "Kundenart_ori" as "Kundenart_ori",
	   "Fabrikat" as "Fabrikat",
	   "Model" as "Model",
	   "Fahrzeug" as "Fahrzeug",
	   "Vehicle Type" as "Vehicle Type",
	   "Market Segment Code" as "Market Segment Code",
	   "Charging Group No" as "Charging Group No",
	   "Cust_No" as "Cust_No",
	   "Cust_Name" as "Cust_Name",
	   "Kunde" as "Kunde",
	   "Cust_Gr_Code" as "Cust_Gr_Code",
	   "Cust_Gr_Description" as "Cust_Gr_Description",
	   "Kundenart" as "Kundenart",
	   "Order Number" as "Order Number",
	   "Lines Net Value_inklType2" as "Lines Net Value_inklType2",
	   "Umsatz Lohn" as "Umsatz Lohn",
	   "Umsatz Teile Service" as "Umsatz Teile Service",
	   "Umsatz Sonstiges" as "Umsatz Sonstiges",
	   "Einsatz Teile Service" as "Einsatz Teile Service",
	   "Einsatz Sonstiges" as "Einsatz Sonstiges",
	   "Menge pro Stunde" as "Menge pro Stunde",
	   "verk. Stunden" as "verk. Stunden",
	   "verk. AW" as "verk. AW",
	   "benutzte Zeit" as "benutzte Zeit",
	   "benutzte AW" as "benutzte AW",
	   "Name_Employee" as "Name_Employee",
	   "Function Code" as "Function Code",
	   "Monteur" as "Monteur",
	   "Funktion Monteur" as "Funktion Monteur",
	   "ben. AW ohne Azubi" as "ben. AW ohne Azubi",
	   "Auftragsdatum" as "Auftragsdatum",
	   "Invoice Date" as "Invoice Date",
	   "Auftragsart" as "Auftragsart",
	   "DG_1" as "DG_1",
	   "DG_2" as "DG_2",
	   "Durchgänge" as "Durchgänge",
	   "Standort" as "Standort",
	   "Auftragsart_1" as "Auftragsart_1",
	   "Menge (Base)" as "Menge (Base)",
	   "Standard Zeit" as "Standard Zeit",
	   "Order Number_Rg_Ausg_ori" as "Order Number_Rg_Ausg_ori",
	   "Lines Net Value" as "Lines Net Value",
	   "Service Advisor No" as "Service Advisor No",
	   "First Name" as "First Name",
	   "Last Name" as "Last Name",
	   "Serviceberater" as "Serviceberater",
	   "gepl. AW-Satz" as "gepl. AW-Satz",
	   "Umsatz Lohn Plan" as "Umsatz Lohn Plan",
	   "Qty Evaluated" as "Qty Evaluated",
	   "Description 2_ori" as "Description 2_ori",
	   "No+Description" as "No+Description",
	   SUM("Umsatz Teile Service") OVER (partition by "Bill-to Name") as "Summe Description 2",
	   "Umsatz Teile Service_Lack" as "Umsatz Teile Service_Lack",
	   "Hauptbetrieb_ID" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name" as "Hauptbetrieb_Name",
	   "Standort_ID" as "Standort_ID",
	   "Standort_Name" as "Standort_Name"
from 
(select "Document Type_2",
	   "No",
	   "Sell-to Customer No",
	   "Bill-to Customer No",
	   "Bill-to Name",
	   "Bill-to Name 2",
	   "Order Date",
	   "Posting Date_2",
	   "Posting Description",
	   "Location Code_2",
	   "Department Code_2",
	   "Make Code",
	   "Customer Posting Group",
	   "Salesperson Code",
	   "Order Class",
	   "Sell-to Customer Name",
	   "Sell-to Customer Name 2",
	   "Document Date",
	   "No Series",
	   "Posting No Series",
	   "Shipping No Series",
	   "Date Archived",
	   "Archived By User",
	   "Reason For Archiving",
	   "Service Posting Group",
	   "Vin",
	   "License No",
	   "Mileage",
	   "Model Code",
	   "Model No",
	   "Prod Year",
	   "Description 2",
	   "Initial Registration",
	   "Gen Prod Posting Group_2",
	   "No Of Cylinders",
	   "Cylinder Capacity (ccm)" as "Cylinder Capacity (ccm)",
	   "Power (kw)" as "Power (kw)",
	   "Power (hp)" as "Power (hp)",
	   "Book No",
	   "Branch Book No",
	   "Sales Department Code_2",
	   "Item Sales Pr Grp Int Chrg",
	   "Int Charg Labor Price Group",
	   "Ext Serv Int Charging Pr Grp",
	   "Post With Book No",
	   "Internal Order",
	   "Branch Code",
	   "Work Completed",
	   "Sales Branch Code_falsch",
	   "Vehicle Status",
	   "Entry No",
	   "Order No",
	   "Posting Date",
	   "Document No",
	   "Type",
	   "No_Product",
	   "Description_Product",
	   "Quantity",
	   "Direct Unit Cost",
	   "Unit Cost",
	   "Total Cost",
	   "Unit Price",
	   "Total Price",
	   "Location Code",
	   "Department Code",
	   "Work Type Code",
	   "User Id",
	   "Amt To Post To G L",
	   "Amt Posted To G L",
	   "Entry Type",
	   "Positive",
	   "Quantity (base)" as "Quantity (base)",
	   "Time Type",
	   "Service Job No",
	   "Sales Department Code",
	   "Qty Per Hour",
	   "Qty (hour)" as "Qty (hour)",
	   "Document Type",
	   "Customer Group Code",
	   "Standard Time",
	   "Standard Time Type",
	   "Order Line No",
	   "Order Completed",
	   "Sales Branch Code",
	   "Hauptbetrieb",
	   "Standort_Department",
	   "Code_Salesperson",
	   "Name_Salesperson",
	   "Name_Salesperson" as "Serviceberater_falsch",
	   "Menge",
	   "Einstandsbetrag",
	   "Verkaufsbetrag",
	   "Gen Bus Posting Group",
	   "Gen Prod Posting Group",
	   "Source Type",
	   "Source No",
	   "Source No (payment)" as "Source No (payment)",
	   "Umsatzart",
	   "Make Code" as "Marke",
	   "Customer Group Code" as "Kundenart_ori",
	   "Fabrikat",
	   "Description 2" as "Model",
	   "Fahrzeug",
	   "Vehicle Type",
	   "Market Segment Code",
	   "Charging Group No",
	   "Cust_No",
	   "Cust_Name",
	   "Kunde",
	   "Cust_Gr_Code",
	   "Cust_Gr_Description",
	   "Kundenart",
	   "No" as "Order Number",
	   "Verkaufsbetrag" as "Lines Net Value_inklType2",
	   "Umsatz Lohn",
	   "Umsatz Teile Service",
	   "Umsatz Sonstiges",
	   "Einsatz Teile Service",
	   "Einsatz Sonstiges",
	   "Menge pro Stunde",
	   "verk. Stunden",
	   "verk. AW",
	   "benutzte Zeit",
	   "benutzte AW",
	   "Name_Employee",
	   "Function Code",
	   "Name_Employee" as "Monteur",
	   "Function Code" as "Funktion Monteur",
	   "ben. AW ohne Azubi",
	   "Order Date" as "Auftragsdatum",
	   "Posting Date_2" as "Invoice Date",
	   "Service Posting Group" as "Auftragsart",
	   1 as "DG_1",
	   COUNT("No") OVER (partition by "No") as "DG_2",
	   1 / (COUNT("No") OVER (partition by "No")) as "Durchgänge",
	   "Standort",
	   'Service' as "Auftragsart_1",
	   "Menge (Base)" as "Menge (Base)",
	   "Standard Zeit",
	   "Order Number_Rg_Ausg_ori",
	   "Lines Net Value",
	   "Service Advisor No",
	   "First Name",
	   "Last Name",
	   "Serviceberater",
	   "gepl. AW-Satz",
	   "Umsatz Lohn Plan",
	   "Qty Evaluated",
	   '' as "Description 2_ori",
	   "No+Description" as "No+Description",
	   "Umsatz Teile Service_Lack",
	   "Hauptbetrieb" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name",
	   "Standort" as "Standort_ID",
	   "Standort_Name"
from 
(select (T1."Bill-to Name") as "Bill-to Name",
	   T1."No_" as "No",
	   CASE WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END)) IN ('80')) THEN ('WTB') ELSE null END as "Standort_Name",
	   (CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) as "Standort",
	   CASE WHEN (T1."Client_DB" = '1') THEN ('AHR') WHEN (T1."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   T1."Client_DB" as "Hauptbetrieb",
	   CASE WHEN ((T2."Type" = 1) and (T1."VIN" = 'WERKSTATTVER_LACK')) THEN (((convert(float, T2."Total Price")))) ELSE (0) END as "Umsatz Teile Service_Lack",
	   T2."No_" + ' - ' + T2."Description" as "No+Description",
	   T2."Qty_ Evaluated" as "Qty Evaluated",
	   ((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) * (CASE WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (8.15) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (7.00) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (7.67) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (9.41) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (8.70) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (8.99) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (9.07) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (7.70) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (8.99) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.00) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.00) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.00) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (8.20) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (8.20) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (8.20) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.70) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.70) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.70) ELSE (0) END) as "Umsatz Lohn Plan",
	   CASE WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (8.15) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (7.00) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (7.67) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (9.41) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (8.70) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (8.99) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (9.07) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (7.70) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('Extern','Intern'))) THEN (8.99) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.00) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.00) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'MECH')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.00) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (8.20) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (8.20) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'KARO')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (8.20) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('10','40'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.70) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('30'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.70) WHEN (((((T2."Type" = 4) and (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) <> 0)) and (T2."Charging Group No_" = 'ELEK')) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) IN ('50'))) and ((CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END) IN ('GWL'))) THEN (7.70) ELSE (0) END as "gepl. AW-Satz",
	   T7."First Name" + ' ' + T7."Last Name" as "Serviceberater",
	   T7."Last Name" as "Last Name",
	   T7."First Name" as "First Name",
	   T1."Service Advisor No_" as "Service Advisor No",
	   CASE WHEN (T2."Type" = 2) THEN (0) ELSE (((convert(float, T2."Total Price")))) END as "Lines Net Value",
	   CASE WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 7) and ((T7."First Name" + ' ' + T7."Last Name") IS NOT NULL)) THEN (T1."No_" + ' - ' + T1."Service Posting Group" + ' - ' + (T7."First Name" + ' ' + T7."Last Name") + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date"))) + ' - ' + T4."Name") WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 7) and ((T7."First Name" + ' ' + T7."Last Name") IS NULL)) THEN (T1."No_" + ' - ' + T1."Service Posting Group" + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date"))) + ' - ' + T4."Name") ELSE null END as "Order Number_Rg_Ausg_ori",
	   CASE WHEN (((CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour")))) < 0) THEN ((convert(float, T2."Standard Time" * -1))) ELSE ((convert(float, T2."Standard Time"))) END as "Standard Zeit",
	   (convert(float, T2."Quantity (Base)")) as "Menge (Base)",
	   T1."Service Posting Group" as "Service Posting Group",
	   T1."Posting Date" as "Posting Date_2",
	   T1."Order Date" as "Order Date",
	   CASE WHEN (not T6."Function Code" IN ('AZUBI','AZUBIG')) THEN (((CASE WHEN (T2."Type" = 2) THEN ((convert(float, T2."Qty_ Evaluated"))) ELSE (0) END) * 12)) ELSE (0) END as "ben. AW ohne Azubi",
	   T6."Function Code" as "Function Code",
	   T6."Name" as "Name_Employee",
	   (CASE WHEN (T2."Type" = 2) THEN ((convert(float, T2."Qty_ Evaluated"))) ELSE (0) END) * 12 as "benutzte AW",
	   CASE WHEN (T2."Type" = 2) THEN ((convert(float, T2."Qty_ Evaluated"))) ELSE (0) END as "benutzte Zeit",
	   (CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T2."Qty_ per Hour"))) as "verk. AW",
	   CASE WHEN (T2."Type" = 4) THEN ((convert(float, T2."Qty_ (Hour)"))) ELSE (0) END as "verk. Stunden",
	   (convert(float, T2."Qty_ per Hour")) as "Menge pro Stunde",
	   CASE WHEN (T2."Entry No_" = 6101488) THEN (958.15) WHEN (T2."Type" IN (0,3)) THEN ((CASE WHEN (((T1."No_" IN ('WAU12105302','WAU12107303','WAU12105478','WAU12302117')) and (T2."No_" = '83220144137'))) THEN (((convert(float, T2."Quantity"))) * 12.41) ELSE ((convert(float, T2."Total Cost"))) END)) ELSE (0) END as "Einsatz Sonstiges",
	   CASE WHEN (T2."Type" = 1) THEN ((CASE WHEN (((T1."No_" IN ('WAU12105302','WAU12107303','WAU12105478','WAU12302117')) and (T2."No_" = '83220144137'))) THEN (((convert(float, T2."Quantity"))) * 12.41) ELSE ((convert(float, T2."Total Cost"))) END)) ELSE (0) END as "Einsatz Teile Service",
	   CASE WHEN (T2."Type" IN (0,3)) THEN (((convert(float, T2."Total Price")))) ELSE (0) END as "Umsatz Sonstiges",
	   CASE WHEN ((T2."Type" = 1) and (T1."VIN" <> 'WERKSTATTVER_LACK')) THEN (((convert(float, T2."Total Price")))) ELSE (0) END as "Umsatz Teile Service",
	   CASE WHEN (T2."Type" = 4) THEN (((convert(float, T2."Total Price")))) ELSE (0) END as "Umsatz Lohn",
	   ((convert(float, T2."Total Price"))) as "Verkaufsbetrag",
	   CASE WHEN (T4."No_" LIKE 'INT%') THEN ('Intern') ELSE (T5."Description") END as "Kundenart",
	   T5."Description" as "Cust_Gr_Description",
	   T5."Code" as "Cust_Gr_Code",
	   T4."No_" + ' - ' + T4."Name" as "Kunde",
	   T4."Name" as "Cust_Name",
	   T4."No_" as "Cust_No",
	   T2."Charging Group No_" as "Charging Group No",
	   T2."Market Segment Code" as "Market Segment Code",
	   T2."Vehicle Type" as "Vehicle Type",
	   T1."VIN" + ' - ' + T1."Model" as "Fahrzeug",
	   T1."Model" as "Description 2",
	   CASE WHEN (T1."Make Code" IN ('BMW','BMW-MINI')) THEN (T1."Make Code") ELSE ('Fremdfabrikat') END as "Fabrikat",
	   T2."Customer Group Code" as "Customer Group Code",
	   T1."Make Code" as "Make Code",
	   CASE WHEN (T2."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T2."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END as "Umsatzart",
	   T2."Source No_ (Payment)" as "Source No (payment)",
	   T2."Source No_" as "Source No",
	   T2."Source Type" as "Source Type",
	   T2."Gen_ Prod_ Posting Group" as "Gen Prod Posting Group",
	   T2."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group",
	   CASE WHEN (((T1."No_" IN ('WAU12105302','WAU12107303','WAU12105478','WAU12302117')) and (T2."No_" = '83220144137'))) THEN (((convert(float, T2."Quantity"))) * 12.41) ELSE ((convert(float, T2."Total Cost"))) END as "Einstandsbetrag",
	   (convert(float, T2."Quantity")) as "Menge",
	   T3."Name" as "Name_Salesperson",
	   T3."Code" as "Code_Salesperson",
	   (left(T2."Department Code",2)) as "Standort_Department",
	   T2."Sales Branch Code" as "Sales Branch Code",
	   T2."Order Completed" as "Order Completed",
	   T2."Order Line No_" as "Order Line No",
	   T2."Standard Time Type" as "Standard Time Type",
	   T2."Standard Time" as "Standard Time",
	   T2."Document Type" as "Document Type",
	   T2."Qty_ (Hour)" as "Qty (hour)",
	   T2."Qty_ per Hour" as "Qty Per Hour",
	   T2."Sales Department Code" as "Sales Department Code",
	   T2."Service Job No_" as "Service Job No",
	   T2."Time Type" as "Time Type",
	   T2."Quantity (Base)" as "Quantity (base)",
	   T2."Positive" as "Positive",
	   T2."Entry Type" as "Entry Type",
	   T2."Amt_ Posted to G_L" as "Amt Posted To G L",
	   T2."Amt_ to Post to G_L" as "Amt To Post To G L",
	   T2."User ID" as "User Id",
	   T2."Work Type Code" as "Work Type Code",
	   T2."Department Code" as "Department Code",
	   T2."Location Code" as "Location Code",
	   T2."Total Price" as "Total Price",
	   T2."Unit Price" as "Unit Price",
	   T2."Total Cost" as "Total Cost",
	   T2."Unit Cost" as "Unit Cost",
	   T2."Direct Unit Cost" as "Direct Unit Cost",
	   T2."Quantity" as "Quantity",
	   T2."Description" as "Description_Product",
	   T2."No_" as "No_Product",
	   T2."Type" as "Type",
	   T2."Document No_" as "Document No",
	   T2."Posting Date" as "Posting Date",
	   T2."Order No_" as "Order No",
	   T2."Entry No_" as "Entry No",
	   T1."Vehicle Status" as "Vehicle Status",
	   T1."Sales Branch Code" as "Sales Branch Code_falsch",
	   T1."Work Completed" as "Work Completed",
	   T1."Branch Code" as "Branch Code",
	   T1."Internal Order" as "Internal Order",
	   T1."Post with Book No_" as "Post With Book No",
	   T1."Ext Serv Int Charging Pr Grp" as "Ext Serv Int Charging Pr Grp",
	   T1."Int_ Charg_ Labor Price Group" as "Int Charg Labor Price Group",
	   T1."Item Sales Pr_ Grp_ Int_ Chrg_" as "Item Sales Pr Grp Int Chrg",
	   T1."Sales Department Code" as "Sales Department Code_2",
	   T1."Branch Book No_" as "Branch Book No",
	   T1."Book No_" as "Book No",
	   T1."Power (hp)" as "Power (hp)",
	   T1."Power (kW)" as "Power (kw)",
	   T1."Cylinder Capacity (ccm)" as "Cylinder Capacity (ccm)",
	   T1."No_ of Cylinders" as "No Of Cylinders",
	   T1."Gen_ Prod_ Posting Group" as "Gen Prod Posting Group_2",
	   T1."Initial Registration" as "Initial Registration",
	   T1."Prod_ Year" as "Prod Year",
	   T1."Model No_" as "Model No",
	   T1."Model Code" as "Model Code",
	   T1."Mileage" as "Mileage",
	   T1."License No_" as "License No",
	   T1."VIN" as "Vin",
	   T1."Reason for Archiving" as "Reason For Archiving",
	   T1."Archived by User" as "Archived By User",
	   T1."Date Archived" as "Date Archived",
	   T1."Shipping No_ Series" as "Shipping No Series",
	   T1."Posting No_ Series" as "Posting No Series",
	   T1."No_ Series" as "No Series",
	   T1."Document Date" as "Document Date",
	   T1."Sell-to Customer Name 2" as "Sell-to Customer Name 2",
	   T1."Sell-to Customer Name" as "Sell-to Customer Name",
	   T1."Order Class" as "Order Class",
	   T1."Salesperson Code" as "Salesperson Code",
	   T1."Customer Posting Group" as "Customer Posting Group",
	   T1."Department Code" as "Department Code_2",
	   T1."Location Code" as "Location Code_2",
	   T1."Posting Description" as "Posting Description",
	   T1."Bill-to Name 2" as "Bill-to Name 2",
	   T1."Bill-to Customer No_" as "Bill-to Customer No",
	   T1."Sell-to Customer No_" as "Sell-to Customer No",
	   T1."Document Type" as "Document Type_2"
from (((("NAVISION"."import"."Archived_Service_Header" T1 left outer join "NAVISION"."import"."Salesperson_Purchaser" T3 on (T1."Salesperson Code" = T3."Code") and (T1."Client_DB" = T3."Client_DB")) left outer join "NAVISION"."import"."Customer" T4 on (T1."Sell-to Customer No_" = T4."No_") and (T1."Client_DB" = T4."Client_DB")) left outer join "NAVISION"."import"."Customer_Group" T5 on (T4."Customer Group Code" = T5."Code") and (T4."Client_DB" = T5."Client_DB")) left outer join "NAVISION"."import"."Employee" T7 on (T1."Service Advisor No_" = T7."No_") and (T1."Client_DB" = T7."Client_DB")),
	("NAVISION"."import"."Service_Ledger_Entry" T2 left outer join "NAVISION"."import"."Employee" T6 on (T6."No_" = T2."No_") and (T6."Client_DB" = T2."Client_DB"))
where ((T1."No_" = T2."Order No_") and (T1."Client_DB" = T2."Client_DB"))
and (((((T1."Document Type" <> 0) and (T1."Posting Date" >= convert(datetime, '2021-01-01 00:00:00.000'))) and (T2."Entry Type" = 0)) and ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') ELSE null END) <> '20')) and (((left(T1."VIN",4)) IN ('AZG_','WMO_','WSL_')) or (T1."VIN" = 'WERKSTATTVER_LACK')))
) D1
) D4
-- order by "Bill-to Name" asc,"Vin" asc,"No" asc,"Service Advisor No" asc,"Order Line No" asc