COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\zeiten\Auftraege_Mont_SPP_1340_verk_AW.imr
TITLE,Auftraege_Mont_SPP_1340_verk_AW.imr
BEGIN SQL
select T1."ORDER_NUMBER" as c1,
	   T1."INVOICE_DATE" as c2,
	   T1."CLIENT_DB" as c3,
	   (substring(T1."DEPARTMENT" from 2 for 1)) as c4,
	   (substring(T1."DEPARTMENT" from 4 for 1)) as c5,
	   T2."Zuordnung" as c6,
	   (rtrim(T3."PROFILE_CODE")) || ' - ' || T4."SEL_NAME" as c7,
	   T5."EDITED_INV_TIME_INT" as c8,
	   T6."WORK_LEADER_GROUP" as c9,
	   T6."WORKLEADER_TEXT" as c10,
	   T6."WORK_LEADER_GROUP" || ' - ' || T6."WORKLEADER_TEXT" as c11,
	   'ben. Zeit' as c12,
	   '' as c13,
	   '' as c14,
	   T7."Hauptbetrieb_ID" as c15,
	   T7."Hauptbetrieb_Name" as c16,
	   T7."Standort_ID" as c17,
	   T7."Standort_Name" as c18,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."INVOICE_DATE")) <= 4) THEN ((od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || T1."MODEL_TEXT" || ' - ' || T1."NAME") ELSE null END as c19
from ((("OPTIMA"."import"."ORDER_HEADER" T1 left outer join "OPTIMA"."data"."GC_Umsatzart" T2 on (T2."Client_DB" = T1."CLIENT_DB") and (T2."Geschaeftsbuchungsgruppe" = T1."CUSTOMER_GROUP")) left outer join "OPTIMA"."import"."ORDER_LINE" T8 on (T1."ORDER_NUMBER" = T8."ORDER_NUMBER") and (T1."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Department" T7 on (od_left(T1."DEPARTMENT",2)) = T7."Standort"),
	(("OPTIMA"."import"."APPORTIONED_INV_TIME" T5 left outer join "OPTIMA"."import"."VPP43" T4 on (T4."SELLER_CODE" = T5."PROFILE_CODE") and (T4."CLIENT_DB" = T5."CLIENT_DB")) left outer join "OPTIMA"."import"."PROFILE" T3 on (T3."PROFILE_CODE" = T4."SELLER_CODE") and (T3."CLIENT_DB" = T4."CLIENT_DB")),
	("OPTIMA"."import"."EMPLOYEE" T9 left outer join "OPTIMA"."import"."VPP91" T6 on (T9."WORK_LEADER_GROUP_ID" = T6."WORK_LEADER_GROUP") and (T9."CLIENT_DB" = T6."CLIENT_DB"))
where (((T8."ORDER_NUMBER" = T5."ORDER_NUMBER") and (T8."UNIQUE_IDENT" = T5."ORDER_LINE_ID")) and (T8."CLIENT_DB" = T5."CLIENT_DB")) and ((T9."PERSON_ID" = T3."PERSON_ID") and (T9."CLIENT_DB" = T3."CLIENT_DB"))
and ((T1."INVOICE_DATE" >= TIMESTAMP '2020-01-01 00:00:00.000') and (T1."STATUS" IN ('35','37','39','47','48','49','50','51','52','36','34')))

END SQL
COLUMN,0,Order Number
COLUMN,1,Datum
COLUMN,2,Hauptbetrieb
COLUMN,3,Marke
COLUMN,4,Kostenstelle
COLUMN,5,Umsatzart
COLUMN,6,Monteur
COLUMN,7,berechn. AW
COLUMN,8,Work Leader Group
COLUMN,9,Workleader Text
COLUMN,10,Monteur_Gruppe
COLUMN,11,Activity_Codes_Group1
COLUMN,12,Activity_Codes_Group2
COLUMN,13,Activity_Desc
COLUMN,14,Hauptbetrieb Id
COLUMN,15,Hauptbetrieb Name
COLUMN,16,Standort Id
COLUMN,17,Standort Name
COLUMN,18,Order Number_Mont