C11/System/OPTIMA/IQD/Zeiten/Schichtplan_pro_MA_neu.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\zeiten\Schichtplan_pro_MA_neu.imr
TITLE,Schichtplan_pro_MA_neu.imr
BEGIN SQL
select T1."REFERENCE_IDENT" as c1,
	   T1."REFERENCE_NAME" as c2,
	   T1."CALENDAR_TYPE_ID" as c3,
	   T1."TRANSACT_DATE" as c4,
	   T2."CALENDAR_INFO_ID" as c5,
	   T2."CALENDAR_TYPE_ID" as c6,
	   T2."RECURRING_PATTERN_ID" as c7,
	   T2."RECURRING_PATTERN_PRIORITY" as c8,
	   T2."TRANSACT_DATE" as c9,
	   T3."SEL_NAME" as c10,
	   (od_right(T2."RECURRING_PATTERN_ID",3)) as c11,
	   T4."CORE_WORKING_HOURS" as c12,
	   T5."WORK_LEADER_GROUP" as c13,
	   T5."WORKLEADER_TEXT" as c14,
	   T5."WORK_LEADER_GROUP" || ' - ' || T5."WORKLEADER_TEXT" as c15,
	   T6."CALENDAR_EVENT_NAME" as c16,
	   T6."EVENT_START" as c17,
	   T6."EVENT_END" as c18,
	   T6."OCCURRENCE" as c19,
	   T6."ALL_DAY_EVENT" as c20,
	   T6."RECURRING_PATTERN_ID" as c21,
	   T6."ACTIVITY_ID" as c22,
	   T6."CALENDAR_INFO_ID" as c23,
	   T6."CALENDAR_TYPE_ID" as c24,
	   T6."RECURRING_TYPE_ID" as c25,
	   T6."HOLIDAY_ID" as c26,
	   (dayofweek(T6."EVENT_START")) as c27,
	   T3."SEL_NAME" as c28,
	   T6."EVENT_START" as c29,
	   T7."PERS_DEPARTMENT" as c30,
	   T4."CLIENT_DB" as c31,
	   (od_left(T3."SEL_DEPARTMENT",2)) as c32,
	   T8."PROFILE_CODE" as c33,
	   (substring(T3."SEL_DEPARTMENT" from 4 for 1)) as c34,
	   T4."CORE_WORKING_HOURS" as c35,
	   '' as c36,
	   T9."WORK_LEADER_GROUP_ID" as c37,
	   T9."JOB_END_DATE" as c38,
	   T4."CORE_WORKING_HOURS" as c39,
	   T4."RECURRING_PATTERN_ID" as c40,
	   T4."CORE_WORKING_HOURS" as c41,
	   T4."WORKINGDAY" as c42,
	   T4."START_DAY_ID" as c43,
	   (rtrim(T8."PROFILE_CODE")) || ' - ' || T3."SEL_NAME" as c44,
	   'Abwesenheit' as c45,
	   'Feiertag' as c46,
	   '' as c47,
	   T10."Hauptbetrieb_ID" as c48,
	   T10."Hauptbetrieb_Name" as c49,
	   T10."Standort_ID" as c50,
	   T10."Standort_Name" as c51
from "OPTIMA"."import"."WORKING_PERIOD" T4,
	"OPTIMA"."import"."EMPLOYEE" T9,
	"OPTIMA"."import"."VPP91" T5,
	"OPTIMA"."import"."CALENDAR_EVENT" T6,
	(("OPTIMA"."import"."CALENDAR_INFO" T1 left outer join "OPTIMA"."import"."CALENDAR_PATTERN" T2 on (T2."CALENDAR_INFO_ID" = T1."REFERENCE_IDENT") and (T2."CLIENT_DB" = T1."CLIENT_DB")) left outer join "OPTIMA"."import"."PERSON_INFO" T7 on (T1."REFERENCE_NAME" = T7."PERSON_NUMBER") and (T1."CLIENT_DB" = T7."CLIENT_DB")),
	(("OPTIMA"."import"."VPP43" T3 left outer join "OPTIMA"."import"."PROFILE" T8 on (T3."SELLER_CODE" = T8."PROFILE_CODE") and (T3."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Department" T10 on (T3."CLIENT_DB" = T10."Hauptbetrieb") and ((od_left(T3."SEL_DEPARTMENT",1)) = T10."Standort"))
where ((T8."PROFILE_CODE" = T1."REFERENCE_NAME") and (T8."CLIENT_DB" = T1."CLIENT_DB")) and ((T2."RECURRING_PATTERN_ID" = T4."RECURRING_PATTERN_ID") and (T2."CLIENT_DB" = T4."CLIENT_DB")) and ((T9."PERSON_ID" = T8."PERSON_ID") and (T9."CLIENT_DB" = T8."CLIENT_DB")) and ((T5."WORK_LEADER_GROUP" = T9."WORK_LEADER_GROUP_ID") and (T5."CLIENT_DB" = T9."CLIENT_DB"))
and ((((((((not T8."PROFILE_CODE" IN ('MARE')) and (T6."HOLIDAY_ID" IS NOT NULL)) and (T6."EVENT_START" >= TIMESTAMP '2020-01-01 00:00:00.000')) and (T6."HOLIDAY_ID" IN (1,3,5,6,7,9,10,12,14,17,18))) and (((dayofweek(T6."EVENT_START"))) BETWEEN 2 AND 6)) and (T6."EVENT_START" < (now()))) and ((T5."WORK_LEADER_GROUP" || ' - ' || T5."WORKLEADER_TEXT") IN ('1000 - Mechanik Menden','1100 - Karosserie Menden','1200 - Lackiererei Menden','2000 - Mechanik Hemer','2100 - Karosserie Hemer','2200 - Lackiererei Hemer','3000 - Mechanik Balve','3100 - karosserie Balve','3200 - Lackiererei Balve'))) and ((T9."JOB_END_DATE" = TIMESTAMP '1800-01-01 00:00:00.000') or (T6."EVENT_START" < T9."JOB_END_DATE")))
order by c2 asc

END SQL
COLUMN,0,Reference Ident
COLUMN,1,Reference Name
COLUMN,2,Calendar Type Id
COLUMN,3,Transact Date
COLUMN,4,Calendar Info Id
COLUMN,5,Calendar Type Id
COLUMN,6,Recurring Pattern Id
COLUMN,7,Recurring Pattern Priority
COLUMN,8,Transact Date
COLUMN,9,Name
COLUMN,10,Pattern_rechts_3
COLUMN,11,Std_Tag
COLUMN,12,Work Leader Group
COLUMN,13,Workleader Text
COLUMN,14,Monteur_Gruppe
COLUMN,15,Calendar Event Name
COLUMN,16,Event Start
COLUMN,17,Event End
COLUMN,18,Occurrence
COLUMN,19,All Day Event
COLUMN,20,Recurring Pattern Id
COLUMN,21,Activity Id
COLUMN,22,Calendar Info Id
COLUMN,23,Calendar Type Id
COLUMN,24,Recurring Type Id
COLUMN,25,Holiday Id
COLUMN,26,Wochentag
COLUMN,27,Monteur_ori
COLUMN,28,Datum
COLUMN,29,Pers Department
COLUMN,30,Hauptbetrieb
COLUMN,31,Standort
COLUMN,32,Profile Code
COLUMN,33,Kostenstelle
COLUMN,34,Feiertag
COLUMN,35,Order Number
COLUMN,36,Work Leader Group Id
COLUMN,37,Job End Date
COLUMN,38,abwesend
COLUMN,39,Recurring Pattern Id_aus_Workin_Period
COLUMN,40,Core Working Hours
COLUMN,41,Workingday
COLUMN,42,Start Day Id
COLUMN,43,Monteur
COLUMN,44,Activity_Codes_Group1
COLUMN,45,Activity_Codes_Group2
COLUMN,46,Activity_Desc
COLUMN,47,Hauptbetrieb Id
COLUMN,48,Hauptbetrieb Name
COLUMN,49,Standort Id
COLUMN,50,Standort Name