C11/System/AUTOLINE/IQD/Zeiten/Abwesenheitszeiten.sql

select T1."Type" as "Type",
	   T1."Resource" as "Resource",
	   T1."Date" as "Date",
	   T1."Active001" as "Active001",
	   T1."Active002" as "Active002",
	   T1."Active003" as "Active003",
	   T1."Active004" as "Active004",
	   T1."Active005" as "Active005",
	   T1."Active006" as "Active006",
	   T1."Active007" as "Active007",
	   T1."Active008" as "Active008",
	   T1."Active009" as "Active009",
	   T1."Active010" as "Active010",
	   T1."Client_DB" as "Client Db",
	   T2."Code" as "Code_Resource",
	   T2."NameDescription" as "Namedescription_Resource",
	   T2."Type" as "Type_Resource",
	   T2."AnalysisCode" as "Analysiscode_Resource",
	   T2."ResignedDate" as "Resigneddate_Resource",
	   T2."NameDescription" as "Monteur",
	   CASE WHEN (T1."Client_DB" BETWEEN '10' AND '19') THEN ('10') WHEN (T1."Client_DB" BETWEEN '20' AND '29') THEN ('20') WHEN (T1."Client_DB" BETWEEN '30' AND '39') THEN ('30') WHEN (T1."Client_DB" BETWEEN '40' AND '49') THEN ('40') WHEN (T1."Client_DB" BETWEEN '50' AND '59') THEN ('50') ELSE null END as "Hauptbetrieb_ID",
	   '' as "Hauptbetrieb_Name",
	   T1."Client_DB" as "Standort_ID",
	   T3."LocationCode" as "Locationcode",
	   T3."Description" as "Description",
	   T3."Description" as "Standort_Name",
	   T1."Resource" + '_' + T1."Client_DB" as "Monteur_eindeutig",
	   'abw.' as "Activity_Codes_Group1",
	   T4."Code" as "Code_Reason",
	   T4."Description001" as "Description001_Reason",
	   T4."Description002" as "Description002_Reason",
	   CASE WHEN (T4."Code" IS NULL) THEN (T1."Active001") ELSE (T4."Code" + ' - ' + T4."Description002") END as "Activity_Codes_Group2",
	   (CASE WHEN (T4."Code" IS NULL) THEN (T1."Active001") ELSE (T4."Code" + ' - ' + T4."Description002") END) as "Activity_Desc",
	   '' as "Kostenstelle",
	   1 as "Abw.",
	   T1."Date" as "Datum",
	   (CASE WHEN (T5."UserID" IS NOT NULL) THEN ('unproduktiv') ELSE ('produktiv') END) as "Monteur_Gruppe",
	   T2."AnalysisCode" as "Monteur_Gruppe_2",
	   T5."UserID" as "Userid",
	   T5."JobTitle" as "Jobtitle",
	   CASE WHEN (T5."UserID" IS NOT NULL) THEN ('unproduktiv') ELSE ('produktiv') END as "produktiv_unproduktiv"
from (((("AUTOLINE"."import"."WL_ExtraAvailability" T1 left outer join "AUTOLINE"."import"."WL_WorkshopResources" T2 on (T1."Resource" = T2."Code") and (T1."Client_DB" = T2."Client_DB")) left outer join "AUTOLINE"."import"."GB_CompanyLocations" T3 on T3."LocationCode" = T2."Client_DB") left outer join "AUTOLINE"."import"."WL_ReasonForXvail" T4 on (T1."Active001" = T4."Code") and (T1."Client_DB" = T4."Client_DB")) left outer join "AUTOLINE"."import"."GB_UserDetails" T5 on (left(T2."Code",6)) = T5."UserID")
where (((T1."Date" >= convert(datetime, '2022-01-01 00:00:00.000')) and (T1."Type" = 'R')) and (T2."Code" IS NOT NULL))