C11/System/OPTIMA/IQD/Zeiten_Vorlage/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",
	'1' AS "Hauptbetrieb",
	(left(T8."DEPARTMENT_TYPE_ID", 2)) AS "Standort",
	T9."PROFILE_CODE" AS "Profile Code",
	(substring(T8."DEPARTMENT_TYPE_ID", 4, 1)) AS "Kostenstelle",
	T4."CORE_WORKING_HOURS" AS "Feiertag",
	'' AS "Order Number",
	T10."WORK_LEADER_GROUP_ID" AS "Work Leader Group Id",
	T10."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(T9."PROFILE_CODE")) + ' - ' + T3."SEL_NAME" AS "Monteur"
FROM "deop06"."dbo"."WORKING_PERIOD" T4,
	"deop06"."dbo"."EMPLOYEE" T10,
	"deop06"."dbo"."vPP91" T5,
	"deop06"."dbo"."CALENDAR_EVENT" T6,
	(
		(
			"deop06"."dbo"."CALENDAR_INFO" T1 LEFT JOIN "deop06"."dbo"."CALENDAR_PATTERN" T2 ON T2."CALENDAR_INFO_ID" = T1."REFERENCE_IDENT"
			) LEFT JOIN "deop06"."dbo"."PERSON_INFO" T7 ON T1."REFERENCE_NAME" = T7."PERSON_NUMBER"
		),
	(
		(
			"deop06"."dbo"."vPP43" T3 LEFT JOIN "deop06"."dbo"."PROFILE" T9 ON T3."SELLER_CODE" = T9."PROFILE_CODE"
			) LEFT JOIN "deop06"."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" >= convert(DATETIME, '2014-01-02 00:00:00.000'))
									)
								AND (T6."HOLIDAY_ID" IN (1, 2, 3, 5, 6, 7, 9, 10, 11, 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 - Kundendienst                  ', '1110 - Kundendienst Azubi            ', '1200 - Karosserie                    ', '1210 - Karosserie Azubi              ', '1300 - Lack                          ', '1310 - Lack Azubi                    ', '2100 - Kundendienst Freystadt        ', '2110 - Kundendienst Azubi Freystadt  ', '2200 - Karosserie Freystadt          ', '2210 - Karosserie Azubi Freystadt    ', '2300 - Lackiererei Freystadt         ', '3100 - Kundendienst Dietfurt         ', '3110 - Kundendienst Azubi Dietfurt   ', '3200 - Karosserie Dietfurt           ', '3210 - Karosserie Azubi Dietfurt     ', '3300 - Lackiererei Dietfurt          '))
					)
				AND (
					(T10."JOB_END_DATE" = convert(DATETIME, '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 (((datepart(weekday, T6."EVENT_START"))) = T4."START_DAY_ID")
		)
	-- order by "Reference Name" asc