COGNOS QUERY STRUCTURE,1,1 DATABASE,GC_LOCOSOFT DATASOURCENAME,C:\GlobalCube\System\LOCOSOFT\IQD\serv_teile\offene_Auftraege_Ums_ben_AW.imr TITLE,offene_Auftraege_Ums_ben_AW.imr BEGIN SQL select c55 as c1, c100 as c2, c99 as c3, c98 as c4, c97 as c5, c96 as c6, c95 as c7, c94 as c8, c93 as c9, c92 as c10, c91 as c11, c90 as c12, c89 as c13, c88 as c14, c87 as c15, c86 as c16, c85 as c17, c84 as c18, c83 as c19, c82 as c20, c81 as c21, c80 as c22, '1' as c23, c79 as c24, c78 as c25, c77 as c26, c76 as c27, c75 as c28, c74 as c29, c73 as c30, c72 as c31, c71 as c32, c70 as c33, c69 as c34, c68 as c35, c67 as c36, 'Service' as c37, c66 as c38, c65 as c39, c65 as c40, c64 as c41, c63 as c42, 1 as c43, XCOUNT(c98 for c55) as c44, c62 as c45, c61 as c46, c56 as c47, c60 as c48, c59 as c49, c58 as c50, c57 / (XCOUNT(c98 for c55)) as c51, c56 as c52 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, (od_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 ((od_left((cast_numberToString(cast_integer(T1."order_number"))),6)) || ' - ' || (CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((od_left((cast_numberToString(cast_integer(T1."order_taking_employee_no"))),4)) || ' - ' || T4."name") END) || ' - ' || T5."family_name" || ' - ' || (asciiz(extract(YEAR FROM T1."order_date"),4) || '-' || asciiz(extract(MONTH FROM T1."order_date"),2) || '-' || asciiz(extract(DAY FROM T1."order_date"),2)) || ' - ' || T6."license_plate") ELSE ((od_left((cast_numberToString(cast_integer(T1."order_number"))),6)) || ' - ' || (CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((od_left((cast_numberToString(cast_integer(T1."order_taking_employee_no"))),4)) || ' - ' || T4."name") END) || ' - ' || T5."family_name" || ' - ' || (asciiz(extract(YEAR FROM T1."order_date"),4) || '-' || asciiz(extract(MONTH FROM T1."order_date"),2) || '-' || asciiz(extract(DAY FROM T1."order_date"),2))) END as c71, (od_left((cast_numberToString(cast_integer(T5."customer_number"))),7)) || ' - ' || 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 ((od_left((cast_numberToString(cast_integer(T1."order_taking_employee_no"))),4)) || ' - ' || T4."name") END as c77, T4."name" as c78, (cast_numberToString(cast_integer(T1."subsidiary"))) as c79, T3."description" as c80, T3."code" as c81, T1."clearing_delay_type" as c82, T1."parts_rebate_group_sell" as c83, T1."holder_number" as c84, T1."paying_customer" as c85, T1."order_customer" as c86, T1."order_mileage" as c87, T1."vehicle_number" as c88, T1."order_delivery_employee_no" as c89, T1."order_taking_employee_no" as c90, T1."order_print_date" as c91, T1."estimated_outbound_time" as c92, T1."estimated_inbound_time" as c93, T1."order_date" as c94, T2."invoice_number" as c95, T2."invoice_type" as c96, T2."is_invoiced" as c97, T1."subsidiary" as c98, T2."labour_type" as c99, T1."order_position" as c100 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 QSS."C:\GlobalCube\System\LOCOSOFT\Catalogs\.\..\IQD\Serv_Teile\ben_AW_Order_Number.ims" T9 on T2."order_number" = T9."order_number") where ((((T2."invoice_number" IS NULL) and (T1."order_date" >= TIMESTAMP '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 END SQL COLUMN,0,Order Number_ori COLUMN,1,Order Position COLUMN,2,Labour Type COLUMN,3,Subsidiary COLUMN,4,Is Invoiced COLUMN,5,Invoice Type COLUMN,6,Invoice Number COLUMN,7,Order Date COLUMN,8,Estimated Inbound Time COLUMN,9,Estimated Outbound Time COLUMN,10,Order Print Date COLUMN,11,Order Taking Employee No COLUMN,12,Order Delivery Employee No COLUMN,13,Vehicle Number COLUMN,14,Order Mileage COLUMN,15,Order Customer COLUMN,16,Paying Customer COLUMN,17,Holder Number COLUMN,18,Parts Rebate Group Sell COLUMN,19,Clearing Delay Type COLUMN,20,Code_Labour_Type COLUMN,21,Description_Labour_Type COLUMN,22,Hauptbetrieb COLUMN,23,Standort COLUMN,24,Name_Serviceberater COLUMN,25,Serviceberater COLUMN,26,Umsatzart COLUMN,27,Customer Number_Cust COLUMN,28,First Name_Cust COLUMN,29,Family Name_Cust COLUMN,30,Kunde COLUMN,31,Order Number COLUMN,32,Internal Number_Vehicle COLUMN,33,Vin_Vehicle COLUMN,34,License Plate_Vehicle COLUMN,35,Make Number_Vehicle COLUMN,36,Auftragsart COLUMN,37,Make Number_Makes COLUMN,38,Description_Makes COLUMN,39,Fabrikat COLUMN,40,Description_Models COLUMN,41,Model COLUMN,42,DG_1 COLUMN,43,DG_2 COLUMN,44,Order Number_Labours COLUMN,45,Order Position_Labours COLUMN,46,Net Price In Order COLUMN,47,Text Line COLUMN,48,Order Number_ben_AW COLUMN,49,Summe Duration Minutes_ben_AW COLUMN,50,ben. Std COLUMN,51,Umsatz Lohn