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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_LOCOSOFT
DATASOURCENAME,C:\GlobalCube\System\LOCOSOFT\IQD\serv_teile\Aftersales_Rechnungen_ben_AW_final.imr
TITLE,Aftersales_Rechnungen_ben_AW_final.imr
BEGIN SQL
select c64 as c1,
	   c63 as c2,
	   c62 as c3,
	   c61 as c4,
	   c60 as c5,
	   c59 as c6,
	   c58 as c7,
	   c57 as c8,
	   '1' as c9,
	   c56 as c10,
	   c55 as c11,
	   c54 as c12,
	   c53 as c13,
	   c53 as c14,
	   c52 as c15,
	   c51 as c16,
	   c50 as c17,
	   c49 as c18,
	   c48 as c19,
	   'Service' as c20,
	   c47 as c21,
	   c46 as c22,
	   c45 as c23,
	   c44 as c24,
	   10 as c25,
	   c43 as c26,
	   c37 as c27,
	   c42 as c28,
	   c41 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"))),9)) || ' - ' || T2."Serviceberater" || ' - ' || (CASE WHEN (T3."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name") END)) ELSE ((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),9)) || ' - ' || (CASE WHEN (T3."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name") END)) END) as c37,
	   T9."Summe_Duration_Minutes" / 60 as c38,
	   T9."order_number" as c39,
	   T9."Summe_Duration_Minutes" as c40,
	   (od_left((cast_numberToString(cast_integer(T8."employee_number"))),4)) || ' - ' || T8."name" as c41,
	   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"))),9)) || ' - ' || T2."Serviceberater" || ' - ' || (CASE WHEN (T3."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name") END)) ELSE ((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),9)) || ' - ' || (CASE WHEN (T3."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name") END)) END)) ELSE null END as c42,
	   ((cast_float(T7."time_units"))) / 10 as c43,
	   (cast_float(T7."time_units")) as c44,
	   T7."time_units" as c45,
	   CASE WHEN (T3."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name") END as c46,
	   T3."zip_code" as c47,
	   CASE WHEN (T4."description" = 'Ford') THEN ('1') WHEN (T4."description" = 'Nissan') THEN ('3') WHEN (T4."description" = 'Suzuki') THEN ('2') ELSE ('9') END as c48,
	   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 c49,
	   (od_left((od_left((cast_numberToString(cast_integer(T1."vehicle_number"))),5)) || ' - ' || T5."description",100)) as c50,
	   (od_left((ucase(T5."description")),3)) as c51,
	   T5."description" as c52,
	   T4."description" as c53,
	   CASE WHEN (T1."invoice_type" = 6) THEN ('GWL') WHEN ((T1."invoice_type" = 4) or (T3."customer_number" = 100001)) THEN ('intern') ELSE ('extern') END as c54,
	   T2."Serviceberater" as c55,
	   (cast_numberToString(cast_integer(T1."subsidiary"))) as c56,
	   (od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || '_' || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),9)) as c57,
	   T1."vehicle_number" as c58,
	   T1."is_canceled" as c59,
	   T1."service_date" as c60,
	   T1."invoice_date" as c61,
	   T1."subsidiary" as c62,
	   T1."invoice_number" as c63,
	   T1."invoice_type" as c64
from QSS."C:\GlobalCube\System\LOCOSOFT\Catalogs\..\IQD\Serv_Teile\ben_AW_Order_Number.ims" T9,
	(((((((("dbo"."invoices" T1 left outer join "dbo"."customers_suppliers" T3 on T3."customer_number" = T1."paying_customer") left outer join "dbo"."vehicles" T10 on T1."vehicle_number" = T10."internal_number") left outer join "dbo"."makes" T4 on T10."make_number" = T4."make_number") left outer join "dbo"."models" T5 on (T10."make_number" = T5."make_number") and (T10."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 "dbo"."employees" T8 on T1."creating_employee" = T8."employee_number") 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" = T9."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.