C11/System/LOCOSOFT/IQD/Serv_Teile/offene_Auftraege.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Locosoft_GC
DATASOURCENAME,C:\Gaps_Locosoft_neu\Portal\System\IQD\Serv_Teile\offene_Auftraege.imr
TITLE,offene_Auftraege.imr
BEGIN SQL
select c53 as c1,
	   c94 as c2,
	   c93 as c3,
	   c92 as c4,
	   c91 as c5,
	   c90 as c6,
	   c89 as c7,
	   c88 as c8,
	   c87 as c9,
	   c86 as c10,
	   c85 as c11,
	   c84 as c12,
	   c83 as c13,
	   c82 as c14,
	   c81 as c15,
	   c80 as c16,
	   c79 as c17,
	   c78 as c18,
	   c77 as c19,
	   c76 as c20,
	   c75 as c21,
	   c74 as c22,
	   c73 as c23,
	   c72 as c24,
	   '1' as c25,
	   c71 as c26,
	   c70 as c27,
	   c69 as c28,
	   c68 as c29,
	   c67 as c30,
	   c66 as c31,
	   c65 as c32,
	   c64 as c33,
	   c63 as c34,
	   c62 as c35,
	   c61 as c36,
	   c60 as c37,
	   c59 as c38,
	   'Service' as c39,
	   c58 as c40,
	   c57 as c41,
	   c57 as c42,
	   c56 as c43,
	   c55 as c44,
	   1 as c45,
	   XCOUNT(c93 for c53) as c46,
	   1 / (XCOUNT(c93 for c53)) as c47,
	   c54 as c48,
	   (c54) / (XCOUNT(c93 for c53)) as c49
from 
(select T1."order_number" as c53,
	   (extract(DAY FROM (now()) - T1."order_date")) as c54,
	   (od_left((ucase(T7."description")),3)) as c55,
	   T7."description" as c56,
	   T6."description" as c57,
	   T6."make_number" as c58,
	   T5."make_number" as c59,
	   T5."license_plate" as c60,
	   T5."vin" as c61,
	   T5."internal_number" as c62,
	   CASE WHEN (T5."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"))),3)) || ' - ' || T3."name") END) || ' - ' || T4."family_name" || ' - ' || T5."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"))),3)) || ' - ' || T3."name") END) || ' - ' || T4."family_name") END as c63,
	   (od_left((cast_numberToString(cast_integer(T4."customer_number"))),7)) || ' - ' || T4."first_name" || ' ' || T4."family_name" as c64,
	   T4."family_name" as c65,
	   T4."first_name" as c66,
	   T4."customer_number" as c67,
	   T2."code" || ' - ' || T2."description" as c68,
	   CASE WHEN (T1."order_taking_employee_no" = 0) THEN ('SB fehlt') ELSE ((od_left((cast_numberToString(cast_integer(T1."order_taking_employee_no"))),3)) || ' - ' || T3."name") END as c69,
	   T3."name" as c70,
	   '0' || (cast_numberToString(cast_integer(T1."subsidiary"))) as c71,
	   T2."description" as c72,
	   T2."code" as c73,
	   T1."cdate" as c74,
	   T1."mdate" as c75,
	   T1."clearing_delay_type" as c76,
	   T1."parts_rebate_group_sell" as c77,
	   T1."holder_number" as c78,
	   T1."paying_customer" as c79,
	   T1."order_customer" as c80,
	   T1."order_mileage" as c81,
	   T1."vehicle_number" as c82,
	   T1."order_delivery_employee_no" as c83,
	   T1."order_taking_employee_no" as c84,
	   T1."order_print_date" as c85,
	   T1."estimated_outbound_time" as c86,
	   T1."estimated_inbound_time" as c87,
	   T1."order_date" as c88,
	   T1."invoice_number" as c89,
	   T1."invoice_type" as c90,
	   T1."is_invoiced" as c91,
	   T1."subsidiary" as c92,
	   T1."labour_type" as c93,
	   T1."order_position" as c94
from (((((("dbo"."order_positions" T1 left outer join "dbo"."labour_types" T2 on T1."labour_type" = T2."code") 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 (((T1."is_invoiced" = 0) and (T1."order_date" >= TIMESTAMP '2017-01-01 00:00:00.000')) and (T1."labour_type" <> 'F'))
order by c53 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,Mdate
COLUMN,21,Cdate
COLUMN,22,Code_Labour_Type
COLUMN,23,Description_Labour_Type
COLUMN,24,Hauptbetrieb
COLUMN,25,Standort
COLUMN,26,Name_Serviceberater
COLUMN,27,Serviceberater
COLUMN,28,Umsatzart
COLUMN,29,Customer Number_Cust
COLUMN,30,First Name_Cust
COLUMN,31,Family Name_Cust
COLUMN,32,Kunde
COLUMN,33,Order Number
COLUMN,34,Internal Number_Vehicle
COLUMN,35,Vin_Vehicle
COLUMN,36,License Plate_Vehicle
COLUMN,37,Make Number_Vehicle
COLUMN,38,Auftragsart
COLUMN,39,Make Number_Makes
COLUMN,40,Description_Makes
COLUMN,41,Fabrikat
COLUMN,42,Description_Models
COLUMN,43,Model
COLUMN,44,DG_1
COLUMN,45,DG_2
COLUMN,46,DG
COLUMN,47,Anzahl Tage_1
COLUMN,48,Anzahl Tage