GAPS/Portal/System/IQD/Zeiten/Schichtplan_pro_MA_neu.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\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,
	   '1' as c31,
	   (od_left(T8."DEPARTMENT_TYPE_ID",2)) as c32,
	   T9."PROFILE_CODE" || ' - ' || (database()) as c33,
	   (substring(T8."DEPARTMENT_TYPE_ID" from 4 for 1)) as c34,
	   T4."CORE_WORKING_HOURS" as c35,
	   '' as c36,
	   T10."WORK_LEADER_GROUP_ID" as c37,
	   T10."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((T9."PROFILE_CODE" || ' - ' || (database())))) || ' - ' || T3."SEL_NAME" as c44,
	   (database()) as c45
from "deop01"."dbo"."WORKING_PERIOD" T4,
	"deop01"."dbo"."EMPLOYEE" T10,
	"deop01"."dbo"."vPP91" T5,
	"deop01"."dbo"."CALENDAR_EVENT" T6,
	(("deop01"."dbo"."CALENDAR_INFO" T1 left outer join "deop01"."dbo"."CALENDAR_PATTERN" T2 on T2."CALENDAR_INFO_ID" = T1."REFERENCE_IDENT") left outer join "deop01"."dbo"."PERSON_INFO" T7 on T1."REFERENCE_NAME" = T7."PERSON_NUMBER"),
	(("deop01"."dbo"."vPP43" T3 left outer join "deop01"."dbo"."PROFILE" T9 on T3."SELLER_CODE" = T9."PROFILE_CODE") left outer join "deop01"."dbo"."DEPARTMENT_TYPE" T8 on T3."SEL_DEPARTMENT" = T8."DEPARTMENT_TYPE_ID")
where (T9."PROFILE_CODE" = T1."REFERENCE_NAME") and (T2."RECURRING_PATTERN_ID" = T4."RECURRING_PATTERN_ID") and (T10."PERSON_ID" = T9."PERSON_ID") and (T5."WORK_LEADER_GROUP" = T10."WORK_LEADER_GROUP_ID")
and (((((((((T6."HOLIDAY_ID" IS NOT NULL) and (T6."EVENT_START" >= TIMESTAMP '2014-01-02 00:00:00.000')) and (T6."HOLIDAY_ID" IN (1,3,5,6,7,9,12,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 ('1100 - Kundendienst Brunsbüttel      ','1110 - Meister Brunsbüttel','1120 - Auszubildende Brunsbüttel','1130 - Serviceberater/Lager Brb','1200 - Karosserie Brunsbüttel        ','1300 - Lackierei Brunsbüttel         ','2100 - Kundendienst Marne            ','2110 - Meister Marne','2120 - Auszubildende Marne','2130 - Serviceberater/Lager Marne','2200 - Karosserie Marne              ','2300 - Lackierung Marne              ','3100 - Kundendienst Büsum            ','3110 - Meister Büsum','3120 - Auszubildende Büsum','3130 - Serviceberater/Lager Büsum','3200 - Karosserie Büsum              ','3300 - Lackierung Büsum              ','4100 - Kundendienst Meldorf          ','4110 - Meister Meldorf','4120 - Auszubildende Meldorf','4130 - Serviceberater/Lager Meld.','4200 - Karosserie Meldorf            ','4300 - Lackierung Meldorf            '))) and ((T10."JOB_END_DATE" = TIMESTAMP '1800-01-01 00:00:00.000') or (T6."EVENT_START" < T10."JOB_END_DATE"))) and ((T2."RECURRING_PATTERN_PRIORITY" = 0) or (T2."RECURRING_PATTERN_PRIORITY" IS NULL))) and (((dayofweek(T6."EVENT_START"))) = T4."START_DAY_ID"))
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,Mandant