COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\Schichtplan_pro_MA_neu_SollStd.imr
TITLE,Schichtplan_pro_MA_neu_SollStd.imr
BEGIN SQL
select T1."REFERENCE_IDENT" as c1,
	   T1."REFERENCE_NAME" as c2,
	   T1."CALENDAR_TYPE_ID" as c3,
	   T2."CALENDAR_INFO_ID" as c4,
	   T2."CALENDAR_TYPE_ID" as c5,
	   T2."RECURRING_PATTERN_ID" as c6,
	   T2."RECURRING_PATTERN_PRIORITY" as c7,
	   T3."SEL_NAME" as c8,
	   (od_right(T2."RECURRING_PATTERN_ID",3)) as c9,
	   T4."CORE_WORKING_HOURS" as c10,
	   T5."WORK_LEADER_GROUP" as c11,
	   T5."WORKLEADER_TEXT" as c12,
	   T5."WORK_LEADER_GROUP" || ' - ' || T5."WORKLEADER_TEXT" as c13,
	   T3."SEL_NAME" as c14,
	   T6."PERS_DEPARTMENT" as c15,
	   '1' as c16,
	   '01' as c17,
	   T7."PROFILE_CODE" as c18,
	   CASE WHEN ((substring(T3."SEL_DEPARTMENT" from 3 for 2)) = '20') THEN ('2') WHEN ((substring(T3."SEL_DEPARTMENT" from 3 for 2)) IN ('30','80')) THEN ('6') WHEN ((substring(T3."SEL_DEPARTMENT" from 3 for 2)) = '40') THEN ('3') WHEN ((substring(T3."SEL_DEPARTMENT" from 3 for 2)) = '41') THEN ('4') WHEN (T3."SEL_DEPARTMENT" IN ('1F  ')) THEN ('3') ELSE null END as c19,
	   '' as c20,
	   T8."WORK_LEADER_GROUP_ID" as c21,
	   T8."JOB_END_DATE" as c22,
	   T4."RECURRING_PATTERN_ID" as c23,
	   T4."CORE_WORKING_HOURS" as c24,
	   T4."WORKINGDAY" as c25,
	   T4."START_DAY_ID" as c26,
	   T4."CORE_WORKING_HOURS" as c27,
	   T9."datum" as c28,
	   T9."wochentage_id" as c29,
	   (dayofweek(T8."JOB_END_DATE")) as c30,
	   T3."SEL_NAME" || ' - ' || T7."PROFILE_CODE" as c31
from "deop01"."dbo"."WORKING_PERIOD" T4,
	"deop01"."dbo"."EMPLOYEE" T8,
	"deop01"."dbo"."vPP91" T5,
	QSS."C:\GAPS\Portal\System\IQD\Zeiten\current_date_Schichtplan.ims" T9,
	(("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" T6 on T1."REFERENCE_NAME" = T6."PERSON_NUMBER"),
	("deop01"."dbo"."vPP43" T3 left outer join "deop01"."dbo"."PROFILE" T7 on T3."SELLER_CODE" = T7."PROFILE_CODE")
where (T7."PROFILE_CODE" = T1."REFERENCE_NAME") and (T2."RECURRING_PATTERN_ID" = T4."RECURRING_PATTERN_ID") and (T8."PERSON_ID" = T7."PERSON_ID") and (T5."WORK_LEADER_GROUP" = T8."WORK_LEADER_GROUP_ID") and (T4."START_DAY_ID" = T9."wochentage_id" + 1)
and ((((T5."WORK_LEADER_GROUP" || ' - ' || T5."WORKLEADER_TEXT") IN ('1000 - Kundendienst                  ','2000 - Karosserie                    ','3000 - Auszubildender')) and (T9."datum" <= (@CURRENT_DATE) - INTERVAL '001 00:00:00.000')) and ((T2."RECURRING_PATTERN_PRIORITY" = 0) or (T2."RECURRING_PATTERN_PRIORITY" IS NULL)))
order by c2 asc,c28 asc

END SQL
COLUMN,0,Reference Ident
COLUMN,1,Reference Name
COLUMN,2,Calendar Type Id
COLUMN,3,Calendar Info Id
COLUMN,4,Calendar Type Id
COLUMN,5,Recurring Pattern Id
COLUMN,6,Recurring Pattern Priority
COLUMN,7,Name
COLUMN,8,Pattern_rechts_3
COLUMN,9,Std_Tag
COLUMN,10,Work Leader Group
COLUMN,11,Workleader Text
COLUMN,12,Monteur_Gruppe
COLUMN,13,Monteur_ori
COLUMN,14,Pers Department
COLUMN,15,Hauptbetrieb
COLUMN,16,Standort
COLUMN,17,Profile Code
COLUMN,18,Kostenstelle
COLUMN,19,Order Number
COLUMN,20,Work Leader Group Id
COLUMN,21,Job End Date
COLUMN,22,Recurring Pattern Id_aus_Workin_Period
COLUMN,23,Core Working Hours
COLUMN,24,Workingday
COLUMN,25,Start Day Id
COLUMN,26,Soll Std.
COLUMN,27,Datum
COLUMN,28,Wochentage Id
COLUMN,29,WOCHENTAG (  Job End Date  )
COLUMN,30,Monteur