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

select T1."Resource" as "Resource",
	   T1."DateIn" as "Datein",
	   T1."TimeIn" as "Timein",
	   T1."Sequence" as "Sequence",
	   T1."WIPNumber" as "Wipnumber",
	   T1."WIPLine" as "Wipline",
	   T1."MultipleCount" as "Multiplecount",
	   T1."IdleCode" as "Idlecode",
	   T1."DateOut" as "Dateout",
	   T1."TimeOut" as "Timeout",
	   T1."WhyClockedOff" as "Whyclockedoff",
	   T1."AdjMode" as "Adjmode",
	   T1."HoursAllowed" as "Hoursallowed",
	   T1."HoursTaken" as "Hourstaken",
	   T1."AdjUser" as "Adjuser",
	   T1."AdjDate" as "Adjdate",
	   T1."AdjTime" as "Adjtime",
	   T1."AdjDescription" as "Adjdescription",
	   T1."ResourceAnalysis" as "Resourceanalysis",
	   T1."TerminalID" as "Terminalid",
	   T1."ResourceTeam" as "Resourceteam",
	   T1."State" as "State",
	   T1."CostPerHour" as "Costperhour",
	   T1."ResourceShift" as "Resourceshift",
	   T1."Factor" as "Factor",
	   T1."BonusTime" as "Bonustime",
	   T1."Client_DB" as "Client Db",
	   T2."Code" as "Code_Resource",
	   T2."NameDescription" as "Namedescription_Resource",
	   T2."AnalysisCode" as "Analysiscode_Resource",
	   T2."ResignedDate" as "Resigneddate_Resource",
	   T2."Client_DB" as "Client Db_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",
	   T2."Client_DB" as "Standort_ID",
	   T3."LocationCode" as "Locationcode",
	   T3."Description" as "Description",
	   T3."Description" as "Standort_Name",
	   (CASE WHEN (T4."UserID" IS NOT NULL) THEN ('unproduktiv') ELSE ('produktiv') END) as "Monteur_Gruppe",
	   T5."Code" as "Code_Team",
	   T5."Description001" as "Description001_Team",
	   T5."Description002" as "Description002_Team",
	   CASE WHEN (T4."UserID" IS NOT NULL) THEN ('unproduktiv') ELSE ('produktiv') END as "produktiv_unproduktiv",
	   T2."AnalysisCode" as "Monteur_Gruppe_2",
	   T6."Code" as "Code_Idle",
	   T6."Description001" as "Description001_Idle",
	   T6."Description002" as "Description002_Idle",
	   T6."Description003" as "Description003_Idle",
	   CASE WHEN (T1."IdleCode" IN (' ')) THEN ('prod.') WHEN (not T1."IdleCode" IN (' ')) THEN ('unprod.') ELSE null END as "Activity_Codes_Group1",
	   T6."Code" + ' - ' + T6."Description002" as "Activity_Codes_Group2",
	   (T6."Code" + ' - ' + T6."Description002") as "Activity_Desc",
	   T1."WIPNumber" as "Order Number",
	   '' as "Kostenstelle",
	   T1."DateIn" as "Datum",
	   CASE WHEN ((CASE WHEN (T1."IdleCode" IN (' ')) THEN ('prod.') WHEN (not T1."IdleCode" IN (' ')) THEN ('unprod.') ELSE null END) = 'prod.') THEN (T1."HoursTaken") ELSE (0) END as "prod.",
	   CASE WHEN ((CASE WHEN (T1."IdleCode" IN (' ')) THEN ('prod.') WHEN (not T1."IdleCode" IN (' ')) THEN ('unprod.') ELSE null END) = 'unprod.') THEN (T1."HoursTaken") ELSE (0) END as "unprod.",
	   CASE WHEN ((CASE WHEN (T1."IdleCode" IN (' ')) THEN ('prod.') WHEN (not T1."IdleCode" IN (' ')) THEN ('unprod.') ELSE null END) = 'prod.') THEN (T1."HoursAllowed") ELSE (0) END as "verr Zeit",
	   CASE WHEN ((CASE WHEN (T1."IdleCode" IN (' ')) THEN ('prod.') WHEN (not T1."IdleCode" IN (' ')) THEN ('unprod.') ELSE null END) = 'prod.') THEN (T1."HoursTaken") ELSE (0) END as "benutzte Zeit",
	   T1."Resource" + '_' + T1."Client_DB" as "Monteur_eindeutig",
	   T4."UserID" as "Userid",
	   T4."JobTitle" as "Jobtitle"
from ((((("AUTOLINE"."import"."WL_ClockEntries" T1 left outer join "AUTOLINE"."import"."WL_WorkshopResources" T2 on (T2."Code" = T1."Resource") and (T2."Client_DB" = T1."Client_DB")) left outer join "AUTOLINE"."import"."GB_CompanyLocations" T3 on T3."LocationCode" = T2."Client_DB") left outer join "AUTOLINE"."import"."GB_UserDetails" T4 on (left(T2."Code",6)) = T4."UserID") left outer join "AUTOLINE"."import"."WL_Teams" T5 on (T1."ResourceTeam" = T5."Code") and (T1."Client_DB" = T5."Client_DB")) left outer join "AUTOLINE"."import"."WL_IdleTimeCodes" T6 on (T6."Code" = T1."IdleCode") and (T6."Client_DB" = T1."Client_DB"))
where ((T1."DateIn" >= convert(datetime, '2022-01-01 00:00:00.000')) and (T2."Code" IS NOT NULL))
-- order by "Resource" asc,"Datein" asc