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

select T1."REFERENCE_IDENT" as "Reference Ident",
	   T1."REFERENCE_NAME" as "Reference Name",
	   T1."CALENDAR_TYPE_ID" as "Calendar Type Id_3",
	   T1."TRANSACT_DATE" as "Transact Date_2",
	   T2."CALENDAR_INFO_ID" as "Calendar Info Id_2",
	   T2."CALENDAR_TYPE_ID" as "Calendar Type Id_2",
	   T2."RECURRING_PATTERN_ID" as "Recurring Pattern Id_2",
	   T2."RECURRING_PATTERN_PRIORITY" as "Recurring Pattern Priority",
	   T2."TRANSACT_DATE" as "Transact Date",
	   T3."SEL_NAME" as "Name",
	   (right(T2."RECURRING_PATTERN_ID",3)) as "Pattern_rechts_3",
	   T4."CORE_WORKING_HOURS" as "Std_Tag",
	   T5."WORK_LEADER_GROUP" as "Work Leader Group",
	   T5."WORKLEADER_TEXT" as "Workleader Text",
	   T5."WORK_LEADER_GROUP" + ' - ' + T5."WORKLEADER_TEXT" as "Monteur_Gruppe",
	   T6."CALENDAR_EVENT_NAME" as "Calendar Event Name",
	   T6."EVENT_START" as "Event Start",
	   T6."EVENT_END" as "Event End",
	   T6."OCCURRENCE" as "Occurrence",
	   T6."ALL_DAY_EVENT" as "All Day Event",
	   T6."RECURRING_PATTERN_ID" as "Recurring Pattern Id",
	   T6."ACTIVITY_ID" as "Activity Id",
	   T6."CALENDAR_INFO_ID" as "Calendar Info Id",
	   T6."CALENDAR_TYPE_ID" as "Calendar Type Id",
	   T6."RECURRING_TYPE_ID" as "Recurring Type Id",
	   T6."HOLIDAY_ID" as "Holiday Id",
	   (datepart(weekday, T6."EVENT_START")) as "Wochentag",
	   T3."SEL_NAME" as "Monteur_ori",
	   T6."EVENT_START" as "Datum",
	   T7."PERS_DEPARTMENT" as "Pers Department",
	   T4."CLIENT_DB" as "Hauptbetrieb",
	   (left(T3."SEL_DEPARTMENT",2)) as "Standort",
	   T8."PROFILE_CODE" as "Profile Code",
	   (substring(T3."SEL_DEPARTMENT", 4, 1)) as "Kostenstelle",
	   T4."CORE_WORKING_HOURS" as "Feiertag",
	   '' as "Order Number",
	   T9."WORK_LEADER_GROUP_ID" as "Work Leader Group Id",
	   T9."JOB_END_DATE" as "Job End Date",
	   T4."CORE_WORKING_HOURS" as "abwesend",
	   T4."RECURRING_PATTERN_ID" as "Recurring Pattern Id_aus_Workin_Period",
	   T4."CORE_WORKING_HOURS" as "Core Working Hours",
	   T4."WORKINGDAY" as "Workingday",
	   T4."START_DAY_ID" as "Start Day Id",
	   (rtrim(T8."PROFILE_CODE")) + ' - ' + T3."SEL_NAME" as "Monteur",
	   'Abwesenheit' as "Activity_Codes_Group1",
	   'Feiertag' as "Activity_Codes_Group2",
	   '' as "Activity_Desc",
	   T10."Hauptbetrieb_ID" as "Hauptbetrieb Id",
	   T10."Hauptbetrieb_Name" as "Hauptbetrieb Name",
	   T10."Standort_ID" as "Standort Id",
	   T10."Standort_Name" as "Standort Name"
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 ((left(T3."SEL_DEPARTMENT",2)) = 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" >= convert(datetime, '2020-01-01 00:00:00.000'))) and (T6."HOLIDAY_ID" IN (1,3,5,6,7,9,10,12,14,17,18))) and (((datepart(weekday, T6."EVENT_START"))) BETWEEN 2 AND 6)) and (T6."EVENT_START" < (getdate()))) and ((T5."WORK_LEADER_GROUP" + ' - ' + T5."WORKLEADER_TEXT") IN ('1100 - LH KDD Mechaniker','1700 - LH VW','2100 - SE KDD Mechaniker','2700 - SE VW','3100 - LÜ KDD Mechaniker','3200 - LÜ KAR','3300 - LÜ LACK','3700 - LÜ VW','4100 - WE KDD Mechaniker','4700 - WE VW','6100 - DO KDD Mechaniker','6700 - DO VW'))) and ((T9."JOB_END_DATE" = convert(datetime, '1800-01-01 00:00:00.000')) or (T6."EVENT_START" < T9."JOB_END_DATE")))
-- order by "Reference Name" asc