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

select "No_2",
	   "Sell-to Customer No_2",
	   "Bill-to Customer No",
	   "Bill-to Name",
	   "Bill-to Name 2",
	   "Order Date",
	   "Posting Date_2",
	   "Posting Description",
	   "Due Date",
	   "Payment Discount %",
	   "Pmt Discount Date",
	   "Location Code_2",
	   "Department Code_3",
	   "Make Code_2",
	   "Customer Posting Group",
	   "Invoice Disc Code",
	   "Cust Item Disc Gr",
	   "Salesperson Code",
	   "Order No_2",
	   "On Hold",
	   "Gen Bus Posting Group",
	   "Sell-to Customer Name",
	   "Sell-to Customer Name 2",
	   "Document Date",
	   "Area_2",
	   "Payment Method Code",
	   "No Series",
	   "Order No Series",
	   "User Id",
	   "Order Type_2",
	   "Service Order No_2",
	   "Item Sales Price Group",
	   "Show Discount",
	   "Customer Group Code",
	   "Service Order No 2",
	   "Service Order Line No_2",
	   "Branch Code",
	   "Vin",
	   "Document No",
	   "Line No",
	   "Sell-to Customer No",
	   "Type",
	   "No",
	   "Location Code",
	   "Posting Group",
	   "Quantity Disc Code",
	   "Description",
	   "Description 2",
	   "Unit Of Measure",
	   "Quantity",
	   "Unit Price",
	   "Unit Cost (lcy)" as "Unit Cost (lcy)",
	   "Vat %",
	   "Quantity Disc %",
	   "Line Discount %",
	   "Line Discount Amount",
	   "Amount",
	   "Amount Including Vat",
	   "Allow Invoice Disc",
	   "Department Code_2",
	   "Make Code",
	   "Price Group Code",
	   "Allow Quantity Disc",
	   "Area",
	   "Unit Cost",
	   "Book No",
	   "Variant Code",
	   "Qty Per Unit Of Measure",
	   "Unit Of Measure Code",
	   "Quantity (base)" as "Quantity (base)",
	   "Order No",
	   "Order Line No",
	   "Posting Date",
	   "Branch Book No",
	   "Order Type",
	   "Item Type",
	   "Service Order No",
	   "Service Order Line No",
	   "Item Group Code",
	   "Menge",
	   "Unit Preis",
	   "Unit Kosten (LCY)" as "Unit Kosten (LCY)",
	   "Line Rabatt Betrag",
	   "Betrag",
	   "Kosten",
	   "Betrag" as "Umsatz Teile Service",
	   "Einsatz Teile Service",
	   "Posting Date_2" as "Invoice Date",
	   '1' as "Hauptbetrieb",
	   "Standort",
	   "First Name",
	   "Last Name",
	   "Serviceberater",
	   "Department Code",
	   "Order Number",
	   '' as "Fabrikat",
	   '' as "Model",
	   '' as "Fahrzeug",
	   "Make Code" as "Marke",
	   'Teile' as "Umsatzart",
	   'Teile' as "Auftragsart",
	   "Kundenart",
	   "Kunde",
	   '' as "Function Code",
	   '' as "Monteur",
	   'Teile' as "Auftragsart_1",
	   "Order Number_Rg_Ausg",
	   1 as "DG_1",
	   COUNT("Service Order No_2") OVER (partition by c119) as "DG_2",
	   1 / (COUNT("Service Order No_2") OVER (partition by c119)) as "Durchgänge",
	   '' as "Rg_Ausgang_Arb_Pos",
	   "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name",
	   "Standort" as "Standort_ID",
	   "Standort_Name"
from 
(select ((T1."Service Order No_" + ' - ' + T1."Bill-to Name")) as c119,
	   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")) <= 30) and ((T3."First Name" + ' ' + T3."Last Name") IS NOT NULL)) THEN (T1."Service Order No_" + ' - ' + (T3."First Name" + ' ' + T3."Last Name") + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date"))) + ' - ' + T1."Bill-to Name") WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 30) and ((T3."First Name" + ' ' + T3."Last Name") IS NULL)) THEN (T1."Service Order No_" + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date"))) + ' - ' + T1."Bill-to Name") ELSE null END as "Order Number_Rg_Ausg",
	   T1."Sell-to Customer No_" + ' - ' + T1."Bill-to Name" as "Kunde",
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T4."Description") END as "Kundenart",
	   T2."Make Code" as "Make Code",
	   T1."Service Order No_" + ' - ' + T1."Bill-to Name" as "Order Number",
	   T3."Department Code" as "Department Code",
	   T3."First Name" + ' ' + T3."Last Name" as "Serviceberater",
	   T3."Last Name" as "Last Name",
	   T3."First Name" as "First Name",
	   T1."Posting Date" as "Posting Date_2",
	   ((convert(float, T2."Quantity"))) * ((convert(float, T2."Unit Cost"))) as "Einsatz Teile Service",
	   ((convert(float, T2."Amount"))) as "Betrag",
	   (convert(float, T2."Unit Cost")) as "Kosten",
	   (convert(float, T2."Line Discount Amount")) as "Line Rabatt Betrag",
	   (convert(float, T2."Unit Cost (LCY)")) as "Unit Kosten (LCY)",
	   (convert(float, T2."Unit Price")) as "Unit Preis",
	   (convert(float, T2."Quantity")) as "Menge",
	   T2."Item Group Code" as "Item Group Code",
	   T2."Service Order Line No_" as "Service Order Line No",
	   T2."Service Order No_" as "Service Order No",
	   T2."Item Type" as "Item Type",
	   T2."Order Type" as "Order Type",
	   T2."Branch Book No_" as "Branch Book No",
	   T2."Posting Date" as "Posting Date",
	   T2."Order Line No_" as "Order Line No",
	   T2."Order No_" as "Order No",
	   T2."Quantity (Base)" as "Quantity (base)",
	   T2."Unit of Measure Code" as "Unit Of Measure Code",
	   T2."Qty_ per Unit of Measure" as "Qty Per Unit Of Measure",
	   T2."Variant Code" as "Variant Code",
	   T2."Book No_" as "Book No",
	   T2."Unit Cost" as "Unit Cost",
	   T2."Area" as "Area",
	   T2."Allow Quantity Disc_" as "Allow Quantity Disc",
	   T2."Price Group Code" as "Price Group Code",
	   T2."Department Code" as "Department Code_2",
	   T2."Allow Invoice Disc_" as "Allow Invoice Disc",
	   T2."Amount Including VAT" as "Amount Including Vat",
	   T2."Amount" as "Amount",
	   T2."Line Discount Amount" as "Line Discount Amount",
	   T2."Line Discount %" as "Line Discount %",
	   T2."Quantity Disc_ %" as "Quantity Disc %",
	   T2."VAT %" as "Vat %",
	   T2."Unit Cost (LCY)" as "Unit Cost (lcy)",
	   T2."Unit Price" as "Unit Price",
	   T2."Quantity" as "Quantity",
	   T2."Unit of Measure" as "Unit Of Measure",
	   T2."Description 2" as "Description 2",
	   T2."Description" as "Description",
	   T2."Quantity Disc_ Code" as "Quantity Disc Code",
	   T2."Posting Group" as "Posting Group",
	   T2."Location Code" as "Location Code",
	   T2."No_" as "No",
	   T2."Type" as "Type",
	   T2."Sell-to Customer No_" as "Sell-to Customer No",
	   T2."Line No_" as "Line No",
	   T2."Document No_" as "Document No",
	   T1."VIN" as "Vin",
	   T1."Branch Code" as "Branch Code",
	   T1."Service Order Line No_" as "Service Order Line No_2",
	   T1."Service Order No_ 2" as "Service Order No 2",
	   T1."Customer Group Code" as "Customer Group Code",
	   T1."Show Discount" as "Show Discount",
	   T1."Item Sales Price Group" as "Item Sales Price Group",
	   T1."Service Order No_" as "Service Order No_2",
	   T1."Order Type" as "Order Type_2",
	   T1."User ID" as "User Id",
	   T1."Order No_ Series" as "Order No Series",
	   T1."No_ Series" as "No Series",
	   T1."Payment Method Code" as "Payment Method Code",
	   T1."Area" as "Area_2",
	   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."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group",
	   T1."On Hold" as "On Hold",
	   T1."Order No_" as "Order No_2",
	   T1."Salesperson Code" as "Salesperson Code",
	   T1."Cust__Item Disc_ Gr_" as "Cust Item Disc Gr",
	   T1."Invoice Disc_ Code" as "Invoice Disc Code",
	   T1."Customer Posting Group" as "Customer Posting Group",
	   T1."Make Code" as "Make Code_2",
	   T1."Department Code" as "Department Code_3",
	   T1."Location Code" as "Location Code_2",
	   T1."Pmt_ Discount Date" as "Pmt Discount Date",
	   T1."Payment Discount %" as "Payment Discount %",
	   T1."Due Date" as "Due Date",
	   T1."Posting Description" as "Posting Description",
	   T1."Order Date" as "Order Date",
	   T1."Bill-to Name 2" as "Bill-to Name 2",
	   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_2",
	   T1."No_" as "No_2"
from "NAVISION"."import"."Sales_Invoice_Line" T2,
	((("NAVISION"."import"."Sales_Invoice_Header" T1 left outer join "NAVISION"."import"."Employee" T3 on (T1."Salesperson Code" = T3."No_") and (T1."Client_DB" = T3."Client_DB")) left outer join "NAVISION"."import"."Customer" T5 on (T1."Bill-to Customer No_" = T5."No_") and (T1."Client_DB" = T5."Client_DB")) left outer join "NAVISION"."import"."Customer_Group" T4 on (T5."Customer Group Code" = T4."Code") and (T5."Client_DB" = T4."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 'VRT%') or (T1."No_" LIKE 'VRGT%')))
) D1
-- order by "Salesperson Code" asc,"Service Order No_2" asc,"Serviceberater" asc,"No_2" asc