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

select distinct "Order Number_ori",
	   "Order Position",
	   '' as "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",
	   '' as "Code_Labour_Type",
	   '' as "Description_Labour_Type",
	   "Hauptbetrieb",
	   "Standort",
	   "Name_Serviceberater",
	   "Serviceberater",
	   'Teile' as "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('') OVER (partition by "Order Number_ori") as "DG_2",
	   "Part Number",
	   "Stock No",
	   "Stock Removal Date",
	   "Amount",
	   "Sum",
	   "Parts Type",
	   "Text Line",
	   "Sum" as "Teile"
from 
(select T1."order_number" as "Order Number_ori",
	   T2."sum" as "Sum",
	   T2."text_line" as "Text Line",
	   T2."parts_type" as "Parts Type",
	   T2."amount" as "Amount",
	   T2."stock_removal_date" as "Stock Removal Date",
	   T2."stock_no" as "Stock No",
	   T2."part_number" as "Part Number",
	   (left((ucase(T7."description")),3)) as "Model",
	   T7."description" as "Description_Models",
	   T6."description" as "Description_Makes",
	   T6."make_number" as "Make Number_Makes",
	   T5."make_number" as "Make Number_Vehicle",
	   T5."license_plate" as "License Plate_Vehicle",
	   T5."vin" as "Vin_Vehicle",
	   T5."internal_number" as "Internal Number_Vehicle",
	   CASE WHEN (T5."license_plate" IS NOT NULL) THEN ((left((((T1."order_number"))),6)) + ' - ' + (CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((left((((T1."order_taking_employee_no"))),4)) + ' - ' + 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((((T1."order_number"))))) + ' - ' + (CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((left((((T1."order_taking_employee_no"))),4)) + ' - ' + 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 "Order Number",
	   (left((((T4."customer_number"))),7)) + ' - ' + T4."first_name" + ' ' + T4."family_name" as "Kunde",
	   T4."family_name" as "Family Name_Cust",
	   T4."first_name" as "First Name_Cust",
	   T4."customer_number" as "Customer Number_Cust",
	   CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((left((((T1."order_taking_employee_no"))),4)) + ' - ' + T3."name") END as "Serviceberater",
	   T3."name" as "Name_Serviceberater",
	   '0' + (((T1."subsidiary"))) as "Standort",
	   CASE WHEN (('0' + (((T1."subsidiary")))) IN ('015                                                                                                                                                                                                                                                            ')) THEN ('15') ELSE ('1') END as "Hauptbetrieb",
	   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."order_position" as "Order Position"
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