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

select distinct c55 as "Order Number_ori",
	   c97 as "Order Position",
	   '' as "Labour Type",
	   c96 as "Subsidiary",
	   c95 as "Is Invoiced",
	   c94 as "Invoice Type",
	   c93 as "Invoice Number",
	   c92 as "Order Date",
	   c91 as "Estimated Inbound Time",
	   c90 as "Estimated Outbound Time",
	   c89 as "Order Print Date",
	   c88 as "Order Taking Employee No",
	   c87 as "Order Delivery Employee No",
	   c86 as "Vehicle Number",
	   c85 as "Order Mileage",
	   c84 as "Order Customer",
	   c83 as "Paying Customer",
	   c82 as "Holder Number",
	   c81 as "Parts Rebate Group Sell",
	   c80 as "Clearing Delay Type",
	   '' as "Code_Labour_Type",
	   '' as "Description_Labour_Type",
	   c79 as "Hauptbetrieb",
	   c78 as "Standort",
	   c77 as "Name_Serviceberater",
	   c76 as "Serviceberater",
	   'Teile' 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('') OVER (partition by c55) as "DG_2",
	   c62 as "Part Number",
	   c61 as "Stock No",
	   c60 as "Stock Removal Date",
	   c59 as "Amount",
	   c56 as "Sum",
	   c58 as "Parts Type",
	   c57 as "Text Line",
	   c56 as "Teile"
from 
(select T1."order_number" as c55,
	   T2."sum" as c56,
	   T2."text_line" as c57,
	   T2."parts_type" as c58,
	   T2."amount" as c59,
	   T2."stock_removal_date" as c60,
	   T2."stock_no" as c61,
	   T2."part_number" as c62,
	   (left((ucase(T7."description")),3)) as c63,
	   T7."description" as c64,
	   T6."description" as c65,
	   T6."make_number" as c66,
	   T5."make_number" as c67,
	   T5."license_plate" as c68,
	   T5."vin" as c69,
	   T5."internal_number" as c70,
	   CASE WHEN (T5."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"))))) + ' - ' + T3."name") END) + ' - ' + T4."family_name" + ' - ' + (convert(varchar(50), year(T1."order_date")) + '-' + convert(varchar(50), month(T1."order_date")) + '-' + convert(varchar(50), day(T1."order_date"))) + ' - ' + T5."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"))))) + ' - ' + T3."name") END) + ' - ' + T4."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), T4."customer_number"))))) + ' - ' + T4."first_name" + ' ' + T4."family_name" as c72,
	   T4."family_name" as c73,
	   T4."first_name" as c74,
	   T4."customer_number" as c75,
	   CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((left(((convert(varchar(50), T1."order_taking_employee_no"))))) + ' - ' + T3."name") END as c76,
	   T3."name" as c77,
	   '0' + ((convert(varchar(50), T1."subsidiary"))) as c78,
	   CASE WHEN (('0' + ((convert(varchar(50), T1."subsidiary")))) IN ('015                                                                                                                                                                                                                                                            ')) THEN ('15') ELSE ('1') END as c79,
	   T1."clearing_delay_type" as c80,
	   T1."parts_rebate_group_sell" as c81,
	   T1."holder_number" as c82,
	   T1."paying_customer" as c83,
	   T1."order_customer" as c84,
	   T1."order_mileage" as c85,
	   T1."vehicle_number" as c86,
	   T1."order_delivery_employee_no" as c87,
	   T1."order_taking_employee_no" as c88,
	   T1."order_print_date" as c89,
	   T1."estimated_outbound_time" as c90,
	   T1."estimated_inbound_time" as c91,
	   T1."order_date" as c92,
	   T2."invoice_number" as c93,
	   T2."invoice_type" as c94,
	   T2."is_invoiced" as c95,
	   T1."subsidiary" as c96,
	   T2."order_position" as c97
from (((((("dbo"."order_positions" T1 left outer join "dbo"."parts" T2 on T1."order_number" = T2."order_number") left outer join "dbo"."employees" T3 on T1."order_taking_employee_no" = T3."employee_number") left outer join "dbo"."customers_suppliers" T4 on T1."order_customer" = T4."customer_number") left outer join "dbo"."vehicles" T5 on T1."vehicle_number" = T5."internal_number") left outer join "dbo"."makes" T6 on T5."make_number" = T6."make_number") left outer join "dbo"."models" T7 on (T5."make_number" = T7."make_number") and (T5."model_code" = T7."model_code"))
where ((((T2."invoice_number" IS NULL) and (T1."order_date" >= convert(datetime, '2017-01-01 00:00:00.000'))) and (T2."sum" IS NOT NULL)) and ('' <> 'F'))
) D1
-- order by "Order Number_ori" asc