COGNOS QUERY STRUCTURE,1,1 DATABASE,Locosoft_GC DATASOURCENAME,C:\GlobalCube\System\LOCOSOFT\IQD\serv_teile\Aftersales_Rechnungen_ben_AW_final.imr TITLE,Aftersales_Rechnungen_ben_AW_final.imr BEGIN SQL select c63 as c1, c62 as c2, c61 as c3, c60 as c4, c59 as c5, c58 as c6, c57 as c7, c56 as c8, '1' as c9, c55 as c10, c54 as c11, c53 as c12, c52 as c13, c52 as c14, c51 as c15, c50 as c16, c49 as c17, c48 as c18, c47 as c19, 'Service' as c20, c46 as c21, c45 as c22, c44 as c23, c43 as c24, 10 as c25, c42 as c26, c37 as c27, c41 as c28, '' as c29, c40 as c30, c39 as c31, 1 as c32, XCOUNT(c39 for c37) as c33, (c38) / (XCOUNT(c39 for c37)) as c34 from (select (CASE WHEN (T2."Serviceberater" IS NOT NULL) THEN ((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),8)) || ' - ' || T2."Serviceberater" || ' - ' || ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name")) ELSE ((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),8)) || ' - ' || ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name")) END) as c37, T8."Summe_Duration_Minutes" / 60 as c38, T8."order_number" as c39, T8."Summe_Duration_Minutes" as c40, CASE WHEN ((extract(DAY FROM (now()) - (cdatetime(T1."invoice_date")))) <= 4) THEN ((CASE WHEN (T2."Serviceberater" IS NOT NULL) THEN ((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),8)) || ' - ' || T2."Serviceberater" || ' - ' || ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name")) ELSE ((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),8)) || ' - ' || ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name")) END)) ELSE null END as c41, ((cast_float(T7."time_units"))) / 10 as c42, (cast_float(T7."time_units")) as c43, T7."time_units" as c44, (od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name" as c45, T3."zip_code" as c46, CASE WHEN (T4."description" = 'Ford') THEN ('1') WHEN (T4."description" = 'Nissan') THEN ('3') WHEN (T4."description" = 'Suzuki') THEN ('2') ELSE ('9') END as c47, CASE WHEN ((od_left((cast_numberToString(cast_integer(T6."type"))),1)) IN ('1','6')) THEN ('41 - After Sales Kundendienst eigene Werkstatt') WHEN (T6."type" IN (40,41,44,45,46,47,48,49,88,89,50,90)) THEN ('41 - After Sales Kundendienst eigene Werkstatt') WHEN ((od_left((cast_numberToString(cast_integer(T6."type"))),1)) IN ('2')) THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung') WHEN (T6."type" IN (42,92)) THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung') WHEN ((od_left((cast_numberToString(cast_integer(T6."type"))),1)) IN ('3')) THEN ('45 - After Sales Kundendienst eigene Lackiererei') WHEN (T6."type" IN (43,93)) THEN ('45 - After Sales Kundendienst eigene Lackiererei') ELSE null END as c48, (od_left((od_left((cast_numberToString(cast_integer(T1."vehicle_number"))),5)) || ' - ' || T5."description",100)) as c49, (od_left((ucase(T5."description")),3)) as c50, T5."description" as c51, T4."description" as c52, CASE WHEN (T1."invoice_type" = 6) THEN ('GWL') WHEN ((T1."invoice_type" = 4) or (T3."customer_number" = 100001)) THEN ('intern') ELSE ('extern') END as c53, T2."Serviceberater" as c54, '0' || (cast_numberToString(cast_integer(T1."subsidiary"))) as c55, (od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || '_' || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),8)) as c56, T1."vehicle_number" as c57, T1."is_canceled" as c58, T1."service_date" as c59, T1."invoice_date" as c60, T1."subsidiary" as c61, T1."invoice_number" as c62, T1."invoice_type" as c63 from QSS."C:\GlobalCube\System\LOCOSOFT\Catalogs\..\IQD\Serv_Teile\ben_AW_Order_Number.ims" T8, ((((((("dbo"."invoices" T1 left outer join "dbo"."customers_suppliers" T3 on T3."customer_number" = T1."paying_customer") left outer join "dbo"."vehicles" T9 on T1."vehicle_number" = T9."internal_number") left outer join "dbo"."makes" T4 on T9."make_number" = T4."make_number") left outer join "dbo"."models" T5 on (T9."make_number" = T5."make_number") and (T9."model_code" = T5."model_code")) left outer join "dbo"."labours" T7 on (T7."invoice_number" = T1."invoice_number") and (T7."invoice_type" = T1."invoice_type")) left outer join "dbo"."charge_type_descriptions" T6 on T6."type" = T7."charge_type") left outer join QSS."C:\GlobalCube\System\LOCOSOFT\Catalogs\..\IQD\Serv_Teile\Serviceberater_Rechnung.ims" T2 on T1."invtype_invnr" = T2."invtype_invnr") where (T7."order_number" = T8."order_number") and ((((T1."invoice_type" BETWEEN 2 AND 6) and (T1."is_canceled" <> 1)) and (T1."invoice_date" >= DATE '2017-01-01')) and ((((cast_float(T7."time_units"))) <> 0) and (((cast_float(T7."time_units"))) IS NOT NULL))) ) D1 order by c8 asc,c2 asc END SQL COLUMN,0,Invoice Type COLUMN,1,Invoice Number COLUMN,2,Subsidiary COLUMN,3,Invoice Date COLUMN,4,Service Date COLUMN,5,Is Canceled COLUMN,6,Vehicle Number COLUMN,7,Invoice_Type_Invoice_Number COLUMN,8,Hauptbetrieb COLUMN,9,Standort COLUMN,10,Serviceberater COLUMN,11,Umsatzart COLUMN,12,Fabrikat COLUMN,13,Description_Makes COLUMN,14,Description_Models COLUMN,15,Model COLUMN,16,Fahrzeug COLUMN,17,Kostenstelle COLUMN,18,Marke COLUMN,19,Auftragsart COLUMN,20,Geschäftsart COLUMN,21,Kunde COLUMN,22,Time Units COLUMN,23,Time Units_Zahl COLUMN,24,AW/Std. COLUMN,25,verk. Std._ COLUMN,26,Order Number COLUMN,27,Order Number_Rg_Ausg COLUMN,28,Kundenart COLUMN,29,Summe Duration Minutes COLUMN,30,Order Number_ben_AW COLUMN,31,Anzahl_Datensätze_1 COLUMN,32,Anzahl_Datensätze_2 COLUMN,33,ben. Std.