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")) + 1 as "Wochentag",
	   T3."SEL_NAME" + ' - ' + T7."PROFILE_CODE" as "Monteur_2",
	   T6."EVENT_START" as "Datum",
	   T8."PERS_DEPARTMENT" as "Pers Department",
	   '1' as "Hauptbetrieb",
	   (left(T3."SEL_DEPARTMENT",2)) as "Standort",
	   T7."PROFILE_CODE" as "Profile Code",
	   CASE WHEN ((substring(T3."SEL_DEPARTMENT", 3, 2)) = '20') THEN ('2') WHEN ((substring(T3."SEL_DEPARTMENT", 3, 2)) IN ('30','80')) THEN ('6') WHEN ((substring(T3."SEL_DEPARTMENT", 3, 2)) = '40') THEN ('3') WHEN ((substring(T3."SEL_DEPARTMENT", 3, 2)) = '41') THEN ('4') WHEN (T3."SEL_DEPARTMENT" IN ('1F  ')) THEN ('3') ELSE null END 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(T7."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" T8 on (T1."REFERENCE_NAME" = T8."PERSON_NUMBER") and (T1."CLIENT_DB" = T8."CLIENT_DB")),
	(("OPTIMA"."import"."VPP43" T3 left outer join "OPTIMA"."import"."PROFILE" T7 on (T3."SELLER_CODE" = T7."PROFILE_CODE") and (T3."CLIENT_DB" = T7."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 ((T7."PROFILE_CODE" = T1."REFERENCE_NAME") and (T7."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" = T7."PERSON_ID") and (T9."CLIENT_DB" = T7."CLIENT_DB")) and ((T5."WORK_LEADER_GROUP" = T9."WORK_LEADER_GROUP_ID") and (T5."CLIENT_DB" = T9."CLIENT_DB"))
and (((((((((T6."HOLIDAY_ID" IS NOT NULL) and (T6."EVENT_START" >= convert(datetime, '2013-01-02 00:00:00.000'))) and (T6."HOLIDAY_ID" IN (20,22,24,25,26,28,29,31,36,37))) and (((datepart(weekday, T6."EVENT_START")) + 1) BETWEEN 2 AND 6)) and (T6."EVENT_START" < (getdate()))) and ((T5."WORK_LEADER_GROUP" + ' - ' + T5."WORKLEADER_TEXT") IN ('110  - WIZ Mechanik                  ','120  - WIZ Karosserie                ','140  - WIZ Lehrlinge                 ','310  - ESW Mechanik                  ','320  - ESW Karosserie                ','340  - ESW Lehrlinge                 '))) and ((T9."JOB_END_DATE" = convert(datetime, '1800-01-01 00:00:00.000')) or (T6."EVENT_START" < T9."JOB_END_DATE"))) and ((T2."RECURRING_PATTERN_PRIORITY" = 0) or (T2."RECURRING_PATTERN_PRIORITY" IS NULL))) and (((datepart(weekday, T6."EVENT_START")) + 1) = T4."START_DAY_ID"))
-- order by "Reference Name" asc