C11/System/LOCOSOFT/IQD/Serv_Teile/offene_Auftraege_Ums_ben_AW.sql

select "Order Number_ori",
	   "Order Position",
	   "Labour Type",
	   "Subsidiary",
	   "Is Invoiced",
	   "Invoice Type",
	   "Invoice Number",
	   "Order Date",
	   "Estimated Inbound Time",
	   "Estimated Outbound Time",
	   "Order Print Date",
	   "Order Taking Employee No",
	   "Order Delivery Employee No",
	   "Vehicle Number",
	   "Order Mileage",
	   "Order Customer",
	   "Paying Customer",
	   "Holder Number",
	   "Parts Rebate Group Sell",
	   "Clearing Delay Type",
	   "Code_Labour_Type",
	   "Description_Labour_Type",
	   "Hauptbetrieb",
	   "Standort",
	   "Name_Serviceberater",
	   "Serviceberater",
	   "Umsatzart",
	   "Customer Number_Cust",
	   "First Name_Cust",
	   "Family Name_Cust",
	   "Kunde",
	   "Order Number",
	   "Internal Number_Vehicle",
	   "Vin_Vehicle",
	   "License Plate_Vehicle",
	   "Make Number_Vehicle",
	   'Service' as "Auftragsart",
	   "Make Number_Makes",
	   "Description_Makes",
	   "Description_Makes" as "Fabrikat",
	   "Description_Models",
	   "Model",
	   1 as "DG_1",
	   COUNT("Subsidiary") OVER (partition by "Order Number_ori") as "DG_2",
	   "Order Number_Labours",
	   "Order Position_Labours",
	   "Net Price In Order",
	   "Text Line",
	   "Order Number_ben_AW",
	   "Summe Duration Minutes_ben_AW",
	   c57 / (COUNT("Subsidiary") OVER (partition by "Order Number_ori")) as "ben. Std",
	   "Net Price In Order" as "Umsatz Lohn"
from 
(select T1."order_number" as "Order Number_ori",
	   T2."net_price_in_order" as "Net Price In Order",
	   T9."Summe_Duration_Minutes" / 60 as c57,
	   T9."Summe_Duration_Minutes" as "Summe Duration Minutes_ben_AW",
	   T9."order_number" as "Order Number_ben_AW",
	   T2."text_line" as "Text Line",
	   T2."order_position" as "Order Position_Labours",
	   T2."order_number" as "Order Number_Labours",
	   (substring((upper(T8."description")), 1, 3)) as "Model",
	   T8."description" as "Description_Models",
	   T7."description" as "Description_Makes",
	   T7."make_number" as "Make Number_Makes",
	   T6."make_number" as "Make Number_Vehicle",
	   T6."license_plate" as "License Plate_Vehicle",
	   T6."vin" as "Vin_Vehicle",
	   T6."internal_number" as "Internal Number_Vehicle",
	   CASE WHEN (T6."license_plate" IS NOT NULL) THEN ((substring((convert(varchar(50), T1."order_number")), 1, 6)) + ' - ' + (CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((substring((convert(varchar(50), T1."order_taking_employee_no")), 1, 4)) + ' - ' + T4."name") END) + ' - ' + T5."family_name" + ' - ' + (convert(varchar(50), year(T1."order_date")) + '-' + convert(varchar(50), month(T1."order_date")) + '-' + convert(varchar(50), day(T1."order_date"))) + ' - ' + T6."license_plate") ELSE ((substring((convert(varchar(50), T1."order_number")), 1, 6)) + ' - ' + (CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((substring((convert(varchar(50), T1."order_taking_employee_no")), 1, 4)) + ' - ' + T4."name") END) + ' - ' + T5."family_name" + ' - ' + (convert(varchar(50), year(T1."order_date")) + '-' + convert(varchar(50), month(T1."order_date")) + '-' + convert(varchar(50), day(T1."order_date")))) END as "Order Number",
	   (substring((convert(varchar(50), T5."customer_number")), 1, 7)) + ' - ' + T5."first_name" + ' ' + T5."family_name" as "Kunde",
	   T5."family_name" as "Family Name_Cust",
	   T5."first_name" as "First Name_Cust",
	   T5."customer_number" as "Customer Number_Cust",
	   T3."code" + ' - ' + T3."description" as "Umsatzart",
	   CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((substring((convert(varchar(50), T1."order_taking_employee_no")), 1, 4)) + ' - ' + T4."name") END as "Serviceberater",
	   T4."name" as "Name_Serviceberater",
	   '0' + (convert(varchar(50), T1."subsidiary")) as "Standort",
	   CASE WHEN (('0' + (convert(varchar(50), T1."subsidiary"))) IN ('015                                                                                                                                                                                                                                                            ')) THEN ('15') ELSE ('1') END as "Hauptbetrieb",
	   T3."description" as "Description_Labour_Type",
	   T3."code" as "Code_Labour_Type",
	   T1."clearing_delay_type" as "Clearing Delay Type",
	   T1."parts_rebate_group_sell" as "Parts Rebate Group Sell",
	   T1."holder_number" as "Holder Number",
	   T1."paying_customer" as "Paying Customer",
	   T1."order_customer" as "Order Customer",
	   T1."order_mileage" as "Order Mileage",
	   T1."vehicle_number" as "Vehicle Number",
	   T1."order_delivery_employee_no" as "Order Delivery Employee No",
	   T1."order_taking_employee_no" as "Order Taking Employee No",
	   T1."order_print_date" as "Order Print Date",
	   T1."estimated_outbound_time" as "Estimated Outbound Time",
	   T1."estimated_inbound_time" as "Estimated Inbound Time",
	   T1."order_date" as "Order Date",
	   T2."invoice_number" as "Invoice Number",
	   T2."invoice_type" as "Invoice Type",
	   T2."is_invoiced" as "Is Invoiced",
	   T1."subsidiary" as "Subsidiary",
	   T2."labour_type" as "Labour Type",
	   T1."order_position" as "Order Position"
from (((((((("dbo"."order_positions" T1 left outer join "dbo"."labours" T2 on T1."order_number" = T2."order_number") left outer join "dbo"."labour_types" T3 on T3."code" = T2."labour_type") left outer join "dbo"."employees" T4 on T1."order_taking_employee_no" = T4."employee_number") left outer join "dbo"."customers_suppliers" T5 on T1."order_customer" = T5."customer_number") left outer join "dbo"."vehicles" T6 on T1."vehicle_number" = T6."internal_number") left outer join "dbo"."makes" T7 on T6."make_number" = T7."make_number") left outer join "dbo"."models" T8 on (T6."make_number" = T8."make_number") and (T6."model_code" = T8."model_code")) left outer join "ims"."ben_AW_Order_Number" T9 on T2."order_number" = T9."order_number")
where ((((T2."invoice_number" IS NULL) and (T1."order_date" >= convert(datetime, '2017-01-01 00:00:00.000'))) and (T2."net_price_in_order" IS NOT NULL)) and (T2."labour_type" <> 'F'))
-- order by "Order Number_ori" asc
) D1