COGNOS QUERY
STRUCTURE,1,1
DATABASE,Locosoft_GC
DATASOURCENAME,C:\Gaps_Locosoft_neu\Portal\System\IQD\Serv_Teile\Aftersales_Rechnungen_verk_AW_final.imr
TITLE,Aftersales_Rechnungen_verk_AW_final.imr
BEGIN SQL
select T1."invoice_type" as c1,
	   T1."invoice_number" as c2,
	   T1."subsidiary" as c3,
	   T1."invoice_date" as c4,
	   T1."service_date" as c5,
	   T1."is_canceled" as c6,
	   T1."vehicle_number" as c7,
	   (od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || '_' || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),6)) as c8,
	   '1' as c9,
	   '0' || (cast_numberToString(cast_integer(T1."subsidiary"))) as c10,
	   T2."Serviceberater" as c11,
	   CASE WHEN (T1."invoice_type" = 6) THEN ('GWL') WHEN ((T1."invoice_type" = 4) or (T3."customer_number" = 100001)) THEN ('intern') ELSE ('extern') END as c12,
	   T4."description" as c13,
	   T4."description" as c14,
	   T5."description" as c15,
	   (od_left((ucase(T5."description")),3)) as c16,
	   (od_left((cast_numberToString(cast_integer(T1."vehicle_number"))),5)) || ' - ' || T5."description" as c17,
	   '41' as c18,
	   CASE WHEN (T4."description" = 'Opel') THEN ('1') WHEN (T4."description" = 'Hyundai') THEN ('3') WHEN (T4."description" = 'Chevrolet') THEN ('2') WHEN (T4."description" = 'Seat') THEN ('4') ELSE null END as c19,
	   'Service' as c20,
	   '' as c21,
	   (od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name" as c22,
	   T6."time_units" as c23,
	   (cast_float(T6."time_units")) as c24,
	   10 as c25,
	   ((cast_float(T6."time_units"))) / 10 as c26,
	   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"))),6)) || ' - ' || 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"))),6)) || ' - ' || ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name")) END as c27,
	   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"))),6)) || ' - ' || 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"))),6)) || ' - ' || ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name")) END)) ELSE null END as c28,
	   '' as c29
from (((((("dbo"."invoices" T1 left outer join "dbo"."customers_suppliers" T3 on T3."customer_number" = T1."paying_customer") left outer join "dbo"."vehicles" T7 on T1."vehicle_number" = T7."internal_number") left outer join "dbo"."makes" T4 on T7."make_number" = T4."make_number") left outer join "dbo"."models" T5 on (T7."make_number" = T5."make_number") and (T7."model_code" = T5."model_code")) left outer join "dbo"."labours" T6 on (T6."invoice_number" = T1."invoice_number") and (T6."invoice_type" = T1."invoice_type")) left outer join QSS."C:\Gaps_Locosoft_neu\Portal\System\IQD\Serv_Teile\Serviceberater_Rechnung.ims" T2 on (od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || '_' || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),6)) = T2."Invoice_Type_Invoice_Number")
where ((((T1."invoice_type" BETWEEN 2 AND 6) and (T1."is_canceled" <> 1)) and (T1."invoice_date" >= DATE '2017-01-01')) and ((((cast_float(T6."time_units"))) <> 0) and (((cast_float(T6."time_units"))) IS NOT NULL)))
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