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

select "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",
	   "Posting Date" as "Posting Date",
	   "Location Code" as "Location Code",
	   "Department Code" as "Department Code",
	   "Make Code" as "Make Code",
	   "Customer Posting Group" as "Customer Posting Group",
	   "Salesperson Code" as "Salesperson Code",
	   "Gen Bus Posting Group" as "Gen Bus Posting Group",
	   "Sell-to Customer Name" as "Sell-to Customer Name",
	   "Document Date" as "Document Date",
	   "User Id" as "User Id",
	   "Order Type" as "Order Type",
	   "Service Order No_ori" as "Service Order No_ori",
	   "Vin" as "Vin",
	   "Model Code" as "Model Code",
	   "Model No" as "Model No",
	   "Prod Year" as "Prod Year",
	   "Model_ori" as "Model_ori",
	   "Type_Header" as "Type_Header",
	   "Initial Registration" as "Initial Registration",
	   "Initial Registration_1" as "Initial Registration_1",
	   "Customer Registration Date" as "Customer Registration Date",
	   "Document No" as "Document No",
	   "Line No" as "Line No",
	   "Type" as "Type",
	   "No_1" as "No_1",
	   "Description" as "Description",
	   "Description 2" as "Description 2",
	   "Quantity" as "Quantity",
	   "Unit Price" as "Unit Price",
	   "Line Discount %" as "Line Discount %",
	   "Line Discount Amount" as "Line Discount Amount",
	   "Amount" as "Amount",
	   "Amount Including Vat" as "Amount Including Vat",
	   "Department Code_1" as "Department Code_1",
	   "Make Code_1" as "Make Code_1",
	   "Inv Discount Amount" as "Inv Discount Amount",
	   "Gen Bus Posting Group_1" as "Gen Bus Posting Group_1",
	   "Gen Prod Posting Group" as "Gen Prod Posting Group",
	   "Unit Cost" as "Unit Cost",
	   "Order No" as "Order No",
	   "Order Line No" as "Order Line No",
	   "Order Type_1" as "Order Type_1",
	   "Item Type" as "Item Type",
	   "Vin_1" as "Vin_1",
	   "Registration Date" as "Registration Date",
	   "Service Order No_1" as "Service Order No_1",
	   "Service Order Line No" as "Service Order Line No",
	   "Labor No" as "Labor No",
	   "Customer Group Code" as "Customer Group Code",
	   "Item Group Code" as "Item Group Code",
	   "No_2" as "No_2",
	   "First Name" as "First Name",
	   "Last Name" as "Last Name",
	   "Serviceberater" as "Serviceberater",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "Umsatzart" as "Umsatzart",
	   "Fabrikat" as "Fabrikat",
	   "Model" as "Model",
	   "Fahrzeug" as "Fahrzeug",
	   "Marke" as "Marke",
	   "Service Posting Group_für_Archiv" as "Service Posting Group_für_Archiv",
	   "Service Posting Group" as "Service Posting Group",
	   "Auftragsart" as "Auftragsart",
	   "Cust_Gr_Code" as "Cust_Gr_Code",
	   "Cust_Gr_Description" as "Cust_Gr_Description",
	   "Kundenart" as "Kundenart",
	   "Cust_No" as "Cust_No",
	   "Cust_Name" as "Cust_Name",
	   "Kunde" as "Kunde",
	   "Auftragsart_1" as "Auftragsart_1",
	   "Function Code" as "Function Code",
	   "Monteur" as "Monteur",
	   "Umsatz Lohn" as "Umsatz Lohn",
	   "Umsatz Teile Service_ori" as "Umsatz Teile Service_ori",
	   "Umsatz Sonstiges_ori" as "Umsatz Sonstiges_ori",
	   "Document No_Service_ledger" as "Document No_Service_ledger",
	   "No_Service_ledger" as "No_Service_ledger",
	   "Total Cost_Service_ledger" as "Total Cost_Service_ledger",
	   "Anzahl Datensätze" as "Anzahl Datensätze",
	   "Umsatz Teile Service" as "Umsatz Teile Service",
	   "Umsatz Sonstiges" as "Umsatz Sonstiges",
	   "Einsatz Teile Service" as "Einsatz Teile Service",
	   "verk. AW" as "verk. AW",
	   "benutzte AW" as "benutzte AW",
	   "Invoice Date" as "Invoice Date",
	   "Order Number" as "Order Number",
	   "Order Number_Rg_Ausg" as "Order Number_Rg_Ausg",
	   "DG1" as "DG1",
	   COUNT("Service Order Line No") OVER (partition by "No") as "DG2",
	   ("DG1" / COUNT("Service Order Line No") OVER (partition by "No")) as "Durchgänge",
	   "Order Number_Rg_Ausg_2" as "Order Number_Rg_Ausg_2",
	   "Order Number_Rg_Ausg_1" as "Order Number_Rg_Ausg_1",
	   "Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export" as "Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export",
	   (("Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export" / (COUNT("Service Order Line No") OVER (partition by "Service Order No_ori"))) * -12) as "benutze AW_Stempelzeiten",
	   COUNT("Service Order Line No") OVER (partition by "Service Order No_ori") as "DG_3",
	   "No_Rechnung" as "No_Rechnung",
	   "Summe Ben Aw" as "Summe Ben Aw",
	   (("Summe Ben Aw" / COUNT("Service Order Line No") OVER (partition by "No")) * "DG1") as "benutze AW wenn_dann",
	   "Umsatz FL" as "Umsatz FL",
	   "Einsatz Sonstiges" as "Einsatz Sonstiges",
	   "Einsatz FL" as "Einsatz FL",
	   "Fahrzeugalter_Tage" as "Fahrzeugalter_Tage",
	   "Fahrzeugalter" as "Fahrzeugalter",
	   "FZG_Altersstaffel" as "FZG_Altersstaffel",
	   "Rechnung_Gutschrift" as "Rechnung_Gutschrift",
	   "PLZ_1_Stelle" as "PLZ_1_Stelle",
	   "PLZ_2_Stelle" as "PLZ_2_Stelle",
	   "PLZ_3_Stelle" as "PLZ_3_Stelle",
	   "PLZ_4_Stelle" as "PLZ_4_Stelle",
	   "PLZ" as "PLZ",
	   "Einsatz_Teile" as "Einsatz_Teile",
	   ("DG1" / COUNT("Service Order Line No") OVER (partition by "No")) as "DG",
	   "Repair_Group_Desc" as "Repair_Group_Desc",
	   "Umsatz Teile" as "Umsatz Teile",
	   "DB1_><_EK" as "DB1_><_EK",
	   "Order_Desc_100" as "Order_Desc_100",
	   "Invoice_Desc_100" as "Invoice_Desc_100",
	   "Order_Desc_30" as "Order_Desc_30",
	   "Invoice_Desc_30" as "Invoice_Desc_30",
	   "Cost_Centre_ID" as "Cost_Centre_ID",
	   "Model_Desc" as "Model_Desc",
	   "Customer_Group_Owner" as "Customer_Group_Owner",
	   "Customer_Name_Owner" as "Customer_Name_Owner",
	   "Fahrgestellnummer" as "Fahrgestellnummer",
	   "Produktbuchungsgruppe" as "Produktbuchungsgruppe",
	   "verk. Std." as "verk. Std.",
	   (("Summe Ben Aw" / COUNT("Service Order Line No") OVER (partition by "No")) * "DG1") as "ben. Zeit",
	   "Parts_Focus_Group" as "Parts_Focus_Group",
	   "Parts_Make_Desc" as "Parts_Make_Desc",
	   "Parts_Group_Desc" as "Parts_Group_Desc",
	   "Rabatt Teile" as "Rabatt Teile",
	   "Rabatt Lohn" as "Rabatt Lohn",
	   "Rabatt Sonst." as "Rabatt Sonst.",
	   "Betrag offen" as "Betrag offen",
	   "Tage bis Rechnung" as "Tage bis Rechnung",
	   "gepl. AW-Satz" as "gepl. AW-Satz",
	   "Umsatz Lohn Plan" as "Umsatz Lohn Plan",
	   "Service Order No" as "Service Order No",
	   "Hauptbetrieb_ID" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name" as "Hauptbetrieb_Name",
	   "Standort_ID" as "Standort_ID",
	   "Standort_Name" as "Standort_Name",
	   "TÜV_Amount" as "TÜV_Amount",
	   "FL_Lack_Amount" as "FL_Lack_Amount",
	   "Mietw_Amount" as "Mietw_Amount",
	   "Umsatz_Sonst_Rest" as "Umsatz_Sonst_Rest"
from 
(select "No",
	   "Sell-to Customer No",
	   "Bill-to Customer No",
	   "Bill-to Name",
	   "Posting Date",
	   "Location Code",
	   "Department Code",
	   "Make Code",
	   "Customer Posting Group",
	   "Salesperson Code",
	   "Gen Bus Posting Group",
	   "Sell-to Customer Name",
	   "Document Date",
	   "User Id",
	   "Order Type",
	   "Service Order No_ori",
	   "Vin",
	   "Model Code",
	   "Model No",
	   "Prod Year",
	   '' as "Model_ori",
	   "Type_Header",
	   "Initial Registration",
	   "Initial Registration" as "Initial Registration_1",
	   "Customer Registration Date",
	   "Document No",
	   "Line No",
	   "Type",
	   "No_1",
	   "Description",
	   '' as "Description 2",
	   "Quantity",
	   "Unit Price",
	   "Line Discount %",
	   "Line Discount Amount",
	   "Amount",
	   "Amount Including Vat",
	   "Department Code_1",
	   "Make Code_1",
	   "Inv Discount Amount",
	   "Gen Bus Posting Group_1",
	   "Gen Prod Posting Group",
	   "Unit Cost",
	   "Order No",
	   "Order Line No",
	   "Order Type_1",
	   "Item Type",
	   "Vin_1",
	   "Registration Date",
	   "Service Order No_1",
	   "Service Order Line No",
	   "Labor No",
	   "Customer Group Code",
	   "Item Group Code",
	   "No_2",
	   "First Name",
	   "Last Name",
	   "Serviceberater",
	   '1' as "Hauptbetrieb",
	   "Standort",
	   "Umsatzart",
	   "Fabrikat",
	   '' as "Model",
	   "Fahrzeug",
	   "Make Code" as "Marke",
	   "Service Posting Group_für_Archiv",
	   "Service Posting Group",
	   "Auftragsart",
	   "Cust_Gr_Code",
	   "Cust_Gr_Description",
	   "Kundenart",
	   "Cust_No",
	   "Cust_Name",
	   "Kunde",
	   '' as "Auftragsart_1",
	   '' as "Function Code",
	   '' as "Monteur",
	   "Umsatz Lohn",
	   "Umsatz Teile Service_ori",
	   "Umsatz Sonstiges_ori",
	   "Document No_Service_ledger",
	   "No_Service_ledger",
	   "Total Cost_Service_ledger",
	   COUNT("No") OVER (partition by c161) as "Anzahl Datensätze",
	   ("Umsatz Teile Service_ori") / (COUNT("No") OVER (partition by c161)) as "Umsatz Teile Service",
	   CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c161))) ELSE (0) END as "Umsatz Sonstiges",
	   CASE WHEN (((("Total Cost_Service_ledger") IS NOT NULL) and (not "Gen Prod Posting Group" LIKE '%FZG%')) and (not "Gen Prod Posting Group" LIKE '%SONST%')) THEN ((("Total Cost_Service_ledger")) / (COUNT("No") OVER (partition by c161))) ELSE (0) END as "Einsatz Teile Service",
	   "verk. AW",
	   0 as "benutzte AW",
	   "Posting Date" as "Invoice Date",
	   "Order Number",
	   "Order Number_Rg_Ausg",
	   -1 as "DG1",
	   "Order Number_Rg_Ausg_2",
	   "Order Number_Rg_Ausg_1",
	   "Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export",
	   "No_Rechnung",
	   "Summe Ben Aw",
	   CASE WHEN ("Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST')) THEN ((("Amount") / (COUNT("No") OVER (partition by c161))) * -1) ELSE (0) END as "Umsatz FL",
	   CASE WHEN (("Umsatz Sonstiges_ori") <> 0) THEN ((c195 / (COUNT("No") OVER (partition by c161))) * -1) ELSE (0) END as "Einsatz Sonstiges",
	   CASE WHEN ((CASE WHEN ("Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST')) THEN ((("Amount") / (COUNT("No") OVER (partition by c161))) * -1) ELSE (0) END) <> 0) THEN ((c195 / (COUNT("No") OVER (partition by c161))) * -1) ELSE (0) END as "Einsatz FL",
	   "Fahrzeugalter_Tage",
	   "Fahrzeugalter",
	   "FZG_Altersstaffel",
	   'Gutschrift' as "Rechnung_Gutschrift",
	   "PLZ_1_Stelle",
	   "PLZ_2_Stelle",
	   "PLZ_3_Stelle",
	   "PLZ_4_Stelle",
	   "PLZ",
	   (CASE WHEN (((("Total Cost_Service_ledger") IS NOT NULL) and (not "Gen Prod Posting Group" LIKE '%FZG%')) and (not "Gen Prod Posting Group" LIKE '%SONST%')) THEN ((("Total Cost_Service_ledger")) / (COUNT("No") OVER (partition by c161))) ELSE (0) END) as "Einsatz_Teile",
	   "Repair_Group_Desc",
	   (("Umsatz Teile Service_ori") / (COUNT("No") OVER (partition by c161))) as "Umsatz Teile",
	   CASE WHEN (((("Umsatz Teile Service_ori") / (COUNT("No") OVER (partition by c161)))) - ((CASE WHEN (((("Total Cost_Service_ledger") IS NOT NULL) and (not "Gen Prod Posting Group" LIKE '%FZG%')) and (not "Gen Prod Posting Group" LIKE '%SONST%')) THEN ((("Total Cost_Service_ledger")) / (COUNT("No") OVER (partition by c161))) ELSE (0) END)) < 0) THEN ('VK < EK') ELSE ('VK > EK') END as "DB1_><_EK",
	   "Order_Desc_100",
	   "Invoice_Desc_100",
	   "Order_Desc_30",
	   "Invoice_Desc_30",
	   "Department Code_1" as "Cost_Centre_ID",
	   '' as "Model_Desc",
	   "Gen Bus Posting Group" as "Customer_Group_Owner",
	   "Cust_Name" as "Customer_Name_Owner",
	   "Vin" as "Fahrgestellnummer",
	   "Gen Prod Posting Group" as "Produktbuchungsgruppe",
	   "verk. AW" as "verk. Std.",
	   '' as "Parts_Focus_Group",
	   '' as "Parts_Make_Desc",
	   '' as "Parts_Group_Desc",
	   CASE WHEN (((("Umsatz Teile Service_ori") / (COUNT("No") OVER (partition by c161)))) <> 0) THEN ((c171)) ELSE (0) END as "Rabatt Teile",
	   "Rabatt Lohn",
	   CASE WHEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c161))) ELSE (0) END) <> 0) THEN ((c171)) ELSE (0) END as "Rabatt Sonst.",
	   0 as "Betrag offen",
	   0 as "Tage bis Rechnung",
	   "gepl. AW-Satz",
	   "Umsatz Lohn Plan",
	   "Service Order No",
	   "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name",
	   "Standort" as "Standort_ID",
	   "Standort_Name",
	   CASE WHEN ("Gen Prod Posting Group" IN ('816_SONST','817_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c161))) ELSE (0) END)) ELSE (0) END as "TÜV_Amount",
	   CASE WHEN ("Gen Prod Posting Group" IN ('821_SONST','822_SONST','823_SONST','824_SONST','825_SONST','826_SONST','827_SONST','828_SONST','829_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c161))) ELSE (0) END)) ELSE (0) END as "FL_Lack_Amount",
	   CASE WHEN ("Gen Prod Posting Group" IN ('879_SONST','881_SONST','882_SONST','883_SONST','884_SONST','889_SONST','894_SONST','897_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c161))) ELSE (0) END)) ELSE (0) END as "Mietw_Amount",
	   (CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c161))) ELSE (0) END) - (CASE WHEN ("Gen Prod Posting Group" IN ('816_SONST','817_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c161))) ELSE (0) END)) ELSE (0) END) - (CASE WHEN ("Gen Prod Posting Group" IN ('821_SONST','822_SONST','823_SONST','824_SONST','825_SONST','826_SONST','827_SONST','828_SONST','829_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c161))) ELSE (0) END)) ELSE (0) END) - (CASE WHEN ("Gen Prod Posting Group" IN ('879_SONST','881_SONST','882_SONST','883_SONST','884_SONST','889_SONST','894_SONST','897_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c161))) ELSE (0) END)) ELSE (0) END) as "Umsatz_Sonst_Rest"
from 
(select T1."Service Order No_" as "Service Order No_ori",
	   T1."No_" as "No",
	   (T2."Document No_" + (((T2."Line No_")))) as c161,
	   T2."Gen_ Prod_ Posting Group" as "Gen Prod Posting Group",
	   CASE WHEN (((T2."Gen_ Prod_ Posting Group" LIKE '%FZG%') or (T2."Gen_ Prod_ Posting Group" LIKE '%VKH%')) or (T2."Gen_ Prod_ Posting Group" LIKE '%SONST%')) THEN (((convert(float, T2."Amount"))) * -1) ELSE (0) END as "Umsatz Sonstiges_ori",
	   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') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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_ID",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 15) THEN (T1."Service Order No_") ELSE null END as "Service Order No",
	   (CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') or ((T2."Labor No_" <> '') and (T2."Gen_ Prod_ Posting Group" <> '821_SONST'))) THEN (((convert(float, T2."Quantity"))) * -1) ELSE (0) END) * (CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') and (((convert(float, T2."Quantity"))) <> 0)) THEN (((convert(float, T2."Unit Price")))) ELSE (0) END) as "Umsatz Lohn Plan",
	   CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') and (((convert(float, T2."Quantity"))) <> 0)) THEN (((convert(float, T2."Unit Price")))) ELSE (0) END as "gepl. AW-Satz",
	   convert(float, T2."Line Discount Amount") as c171,
	   CASE WHEN (((CASE WHEN (T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T2."Amount"))) * -1) ELSE (0) END) <> 0) or ((CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') or ((T2."Labor No_" <> '') and (T2."Gen_ Prod_ Posting Group" <> '821_SONST'))) THEN (((convert(float, T2."Quantity"))) * -1) ELSE (0) END) <> 0)) THEN ((convert(float, T2."Line Discount Amount" * -1))) ELSE (0) END as "Rabatt Lohn",
	   CASE WHEN (T2."Gen_ Prod_ Posting Group" LIKE '%ART%') THEN (((convert(float, T2."Amount"))) * -1) ELSE (0) END as "Umsatz Teile Service_ori",
	   T11."Summe ben_AW" as "Summe Ben Aw",
	   (CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') or ((T2."Labor No_" <> '') and (T2."Gen_ Prod_ Posting Group" <> '821_SONST'))) THEN (((convert(float, T2."Quantity"))) * -1) ELSE (0) END) as "verk. AW",
	   T1."VIN" as "Vin",
	   T8."Name" as "Cust_Name",
	   T1."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group",
	   T2."Department Code" as "Department Code_1",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 30) THEN (T1."No_" + ' - ' + (CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) + ' - ' + T8."Name") ELSE ('Rechnungen älter 30 Tage') END as "Invoice_Desc_30",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 30) THEN (T1."Service Order No_") ELSE ('Aufträge älter 30 Tage') END as "Order_Desc_30",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 100) THEN (T1."No_" + ' - ' + T8."Name") ELSE ('Rechnungen älter 100 Tage') END as "Invoice_Desc_100",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 100) THEN (T1."Service Order No_" + ' - ' + T8."Name") ELSE ('Aufträge älter 100 Tage') END as "Order_Desc_100",
	   (convert(float, T9."Total Cost")) as "Total Cost_Service_ledger",
	   CASE WHEN (T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (T2."Gen_ Prod_ Posting Group") ELSE null END as "Repair_Group_Desc",
	   T8."Post Code" as "PLZ",
	   (left(T8."Post Code",4)) as "PLZ_4_Stelle",
	   (left(T8."Post Code",3)) as "PLZ_3_Stelle",
	   (left(T8."Post Code",2)) as "PLZ_2_Stelle",
	   (left(T8."Post Code",1)) as "PLZ_1_Stelle",
	   CASE WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 BETWEEN 0.01 AND 0.99) THEN ('1') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 BETWEEN 1.00 AND 1.99) THEN ('2') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 BETWEEN 2.00 AND 2.99) THEN ('3') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 BETWEEN 3.00 AND 3.99) THEN ('4') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 BETWEEN 4.00 AND 4.99) THEN ('5') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 BETWEEN 5.00 AND 5.99) THEN ('6') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 BETWEEN 6.00 AND 6.99) THEN ('7') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 BETWEEN 7.00 AND 7.99) THEN ('8') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 BETWEEN 8.00 AND 8.99) THEN ('9') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 BETWEEN 9.00 AND 9.99) THEN ('10') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 > 9.99) THEN ('> 10') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 = 0) THEN ('keine Angabe') ELSE null END as "FZG_Altersstaffel",
	   (CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END) / 365 as "Fahrzeugalter",
	   CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((-1 * datediff(day, T1."Posting Date", T1."Initial Registration"))) ELSE (0) END as "Fahrzeugalter_Tage",
	   (convert(float, T2."Amount")) as "Amount",
	   ((convert(float, T2."Quantity"))) * ((convert(float, T2."Unit Cost"))) as c195,
	   T11."No_" as "No_Rechnung",
	   T10."Summe produktiv für Auftrag" as "Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 5) THEN (T2."Service Order No_") ELSE null END as "Order Number_Rg_Ausg_1",
	   CASE WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 15) and ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NOT NULL)) THEN (T1."No_" + ' - ' + (CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) + ' - ' + T8."Name" + ' - ' + (convert(varchar(50), year(T1."Document Date")) + '-' + convert(varchar(50), month(T1."Document Date")) + '-' + convert(varchar(50), -1 * datediff(day, T1."Document Date")))) WHEN (((day((getdate()), T1."Posting Date")) <= 15) and ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NULL)) THEN (T1."No_" + ' - ' + T8."Name" + ' - ' + (convert(varchar(50), year(T1."Document Date")) + '-' + convert(varchar(50), month(T1."Document Date")) + '-' + convert(varchar(50), day(T1."Document Date")))) ELSE null END as "Order Number_Rg_Ausg_2",
	   CASE WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 4) and ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NOT NULL)) THEN (T1."No_" + ' - ' + T1."Service Order No_" + ' - ' + (CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) + ' - ' + T8."Name") WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 4) and ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NULL)) THEN (T1."No_" + ' - ' + T1."Service Order No_" + ' - ' + T8."Name") ELSE null END as "Order Number_Rg_Ausg",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 180) THEN (T1."No_" + ' - ' + T1."Service Order No_" + ' - ' + T8."Name") ELSE ('Aufträge älter 180 Tage') END as "Order Number",
	   T1."Posting Date" as "Posting Date",
	   T9."No_" as "No_Service_ledger",
	   T9."Document No_" as "Document No_Service_ledger",
	   CASE WHEN (T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T2."Amount"))) * -1) ELSE (0) END as "Umsatz Lohn",
	   T8."No_" + ' - ' + T8."Name" as "Kunde",
	   T8."No_" as "Cust_No",
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T7."Description") END as "Kundenart",
	   T7."Description" as "Cust_Gr_Description",
	   T7."Code" as "Cust_Gr_Code",
	   CASE WHEN (T5."Service Posting Group" IS NULL) THEN (T6."Service Posting Group") ELSE (T5."Service Posting Group") END as "Auftragsart",
	   T6."Service Posting Group" as "Service Posting Group",
	   T5."Service Posting Group" as "Service Posting Group_für_Archiv",
	   T1."Make Code" as "Make Code",
	   T1."VIN" + ' - ' + '' as "Fahrzeug",
	   CASE WHEN (T1."Make Code" IN ('BMW','BMW-MINI','BMWI')) THEN (T1."Make Code") ELSE ('Fremdfabrikat') END as "Fabrikat",
	   CASE WHEN (T1."Customer Posting Group" IN ('PKW_GWL')) THEN ('GWL') ELSE ('Extern') END as "Umsatzart",
	   CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END as "Serviceberater",
	   T3."Last Name" as "Last Name",
	   T3."First Name" as "First Name",
	   T3."No_" as "No_2",
	   T2."Item Group Code" as "Item Group Code",
	   T2."Customer Group Code" as "Customer Group Code",
	   T2."Labor No_" as "Labor No",
	   T2."Service Order Line No_" as "Service Order Line No",
	   T2."Service Order No_" as "Service Order No_1",
	   T2."Registration Date" as "Registration Date",
	   T2."VIN" as "Vin_1",
	   T2."Item Type" as "Item Type",
	   T2."Order Type" as "Order Type_1",
	   T2."Order Line No_" as "Order Line No",
	   T2."Order No_" as "Order No",
	   (convert(float, T2."Unit Cost")) as "Unit Cost",
	   T2."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group_1",
	   T2."Inv_ Discount Amount" as "Inv Discount Amount",
	   T2."Make Code" as "Make Code_1",
	   T2."Amount Including VAT" as "Amount Including Vat",
	   T2."Line Discount Amount" as "Line Discount Amount",
	   T2."Line Discount %" as "Line Discount %",
	   (convert(float, T2."Unit Price")) as "Unit Price",
	   (convert(float, T2."Quantity")) as "Quantity",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 30) THEN ((left(T2."Description",25))) ELSE null END as "Description",
	   T2."No_" as "No_1",
	   T2."Type" as "Type",
	   T2."Line No_" as "Line No",
	   T2."Document No_" as "Document No",
	   T1."Customer Registration Date" as "Customer Registration Date",
	   T1."Initial Registration" as "Initial Registration",
	   T1."Type" as "Type_Header",
	   T1."Prod_ Year" as "Prod Year",
	   T1."Model No_" as "Model No",
	   T1."Model Code" as "Model Code",
	   T1."Order Type" as "Order Type",
	   T1."User ID" as "User Id",
	   T1."Document Date" as "Document Date",
	   T1."Sell-to Customer Name" as "Sell-to Customer Name",
	   T1."Salesperson Code" as "Salesperson Code",
	   T1."Customer Posting Group" as "Customer Posting Group",
	   T1."Department Code" as "Department Code",
	   T1."Location Code" as "Location Code",
	   T1."Bill-to Name" as "Bill-to Name",
	   T1."Bill-to Customer No_" as "Bill-to Customer No",
	   T1."Sell-to Customer No_" as "Sell-to Customer No"
from (((("NAVISION"."import"."Sales_Credit_Memo_Header" T1 left outer join "NAVISION"."import"."Customer" T8 on (T1."Bill-to Customer No_" = T8."No_") and (T1."Client_DB" = T8."Client_DB")) left outer join "NAVISION"."import"."Customer_Group" T7 on (T8."Customer Group Code" = T7."Code") and (T8."Client_DB" = T7."Client_DB")) left outer join "ims"."Stempelzeiten_Monteur_Auftrag_Export_SC_Rg_Ausgang" T10 on T1."Service Order No_" = T10."Order No_") left outer join "ims"."Service_Ausgangsrechnung_Export_ben_AW_fuer_GS" T11 on (T1."Applies-to Doc_ No_" = T11."No_") and (T1."Service Order No_" = T11."Service Order No_")),
	((((("NAVISION"."import"."Sales_Credit_Memo_Line" T2 left outer join "NAVISION"."import"."Service_Header" T6 on (T6."No_" = T2."Service Order No_") and (T6."Client_DB" = T2."Client_DB")) left outer join "NAVISION"."import"."Employee" T3 on (T6."Service Advisor No_" = T3."No_") and (T6."Client_DB" = T3."Client_DB")) left outer join "NAVISION"."import"."Archived_Service_Header" T5 on (T5."No_" = T2."Service Order No_") and (T5."Client_DB" = T2."Client_DB")) left outer join "NAVISION"."import"."Employee" T4 on (T5."Service Advisor No_" = T4."No_") and (T5."Client_DB" = T4."Client_DB")) left outer join "NAVISION"."import"."Service_Ledger_Entry" T9 on (((T2."Document No_" = T9."Document No_") and (T2."Type" = 2)) and (T2."No_" = T9."No_")) and (T9."Client_DB" = T2."Client_DB"))
where ((T1."No_" = T2."Document No_") and (T1."Client_DB" = T2."Client_DB"))
and (((((year(T1."Posting Date")) >= (year((getdate()))) - 1) and (T1."No_" LIKE 'W%')) and (not T2."Type" IN (0,11,12))) and (not T2."No_" IN ('PFAND')))
) D1
) D5
-- order by "No" asc