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

select c55 as "Order Number_ori",
	   c101 as "Order Position",
	   c100 as "Labour Type",
	   c99 as "Subsidiary",
	   c98 as "Is Invoiced",
	   c97 as "Invoice Type",
	   c96 as "Invoice Number",
	   c95 as "Order Date",
	   c94 as "Estimated Inbound Time",
	   c93 as "Estimated Outbound Time",
	   c92 as "Order Print Date",
	   c91 as "Order Taking Employee No",
	   c90 as "Order Delivery Employee No",
	   c89 as "Vehicle Number",
	   c88 as "Order Mileage",
	   c87 as "Order Customer",
	   c86 as "Paying Customer",
	   c85 as "Holder Number",
	   c84 as "Parts Rebate Group Sell",
	   c83 as "Clearing Delay Type",
	   c82 as "Code_Labour_Type",
	   c81 as "Description_Labour_Type",
	   c80 as "Hauptbetrieb",
	   c79 as "Standort",
	   c78 as "Name_Serviceberater",
	   c77 as "Serviceberater",
	   c76 as "Umsatzart",
	   c75 as "Customer Number_Cust",
	   c74 as "First Name_Cust",
	   c73 as "Family Name_Cust",
	   c72 as "Kunde",
	   c71 as "Order Number",
	   c70 as "Internal Number_Vehicle",
	   c69 as "Vin_Vehicle",
	   c68 as "License Plate_Vehicle",
	   c67 as "Make Number_Vehicle",
	   'Service' as "Auftragsart",
	   c66 as "Make Number_Makes",
	   c65 as "Description_Makes",
	   c65 as "Fabrikat",
	   c64 as "Description_Models",
	   c63 as "Model",
	   1 as "DG_1",
	   COUNT(c99) OVER (partition by c55) as "DG_2",
	   c62 as "Order Number_Labours",
	   c61 as "Order Position_Labours",
	   c56 as "Net Price In Order",
	   c60 as "Text Line",
	   c59 as "Order Number_ben_AW",
	   c58 as "Summe Duration Minutes_ben_AW",
	   c57 / (COUNT(c99) OVER (partition by c55)) as "ben. Std",
	   c56 as "Umsatz Lohn"
from 
(select T1."order_number" as c55,
	   T2."net_price_in_order" as c56,
	   T9."Summe_Duration_Minutes" / 60 as c57,
	   T9."Summe_Duration_Minutes" as c58,
	   T9."order_number" as c59,
	   T2."text_line" as c60,
	   T2."order_position" as c61,
	   T2."order_number" as c62,
	   (left((ucase(T8."description")),3)) as c63,
	   T8."description" as c64,
	   T7."description" as c65,
	   T7."make_number" as c66,
	   T6."make_number" as c67,
	   T6."license_plate" as c68,
	   T6."vin" as c69,
	   T6."internal_number" as c70,
	   CASE WHEN (T6."license_plate" IS NOT NULL) THEN ((left(((convert(varchar(50), T1."order_number"))))) + ' - ' + (CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((left(((convert(varchar(50), T1."order_taking_employee_no"))))) + ' - ' + 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 ((left(((convert(varchar(50), T1."order_number"))))) + ' - ' + (CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((left(((convert(varchar(50), T1."order_taking_employee_no"))))) + ' - ' + 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 c71,
	   (left(((convert(varchar(50), T5."customer_number"))))) + ' - ' + T5."first_name" + ' ' + T5."family_name" as c72,
	   T5."family_name" as c73,
	   T5."first_name" as c74,
	   T5."customer_number" as c75,
	   T3."code" + ' - ' + T3."description" as c76,
	   CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((left(((convert(varchar(50), T1."order_taking_employee_no"))))) + ' - ' + T4."name") END as c77,
	   T4."name" as c78,
	   '0' + ((convert(varchar(50), T1."subsidiary"))) as c79,
	   CASE WHEN (('0' + ((convert(varchar(50), T1."subsidiary")))) IN ('015                                                                                                                                                                                                                                                            ')) THEN ('15') ELSE ('1') END as c80,
	   T3."description" as c81,
	   T3."code" as c82,
	   T1."clearing_delay_type" as c83,
	   T1."parts_rebate_group_sell" as c84,
	   T1."holder_number" as c85,
	   T1."paying_customer" as c86,
	   T1."order_customer" as c87,
	   T1."order_mileage" as c88,
	   T1."vehicle_number" as c89,
	   T1."order_delivery_employee_no" as c90,
	   T1."order_taking_employee_no" as c91,
	   T1."order_print_date" as c92,
	   T1."estimated_outbound_time" as c93,
	   T1."estimated_inbound_time" as c94,
	   T1."order_date" as c95,
	   T2."invoice_number" as c96,
	   T2."invoice_type" as c97,
	   T2."is_invoiced" as c98,
	   T1."subsidiary" as c99,
	   T2."labour_type" as c100,
	   T1."order_position" as c101
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 c55 asc
) D1