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 JOIN "OPTIMA"."import"."CALENDAR_PATTERN" T2 ON (T2."CALENDAR_INFO_ID" = T1."REFERENCE_IDENT")
				AND (T2."CLIENT_DB" = T1."CLIENT_DB")
			) LEFT 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 JOIN "OPTIMA"."import"."PROFILE" T8 ON (T3."SELLER_CODE" = T8."PROFILE_CODE")
				AND (T3."CLIENT_DB" = T8."CLIENT_DB")
			) LEFT 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