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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_AUTOLINE
DATASOURCENAME,C:\GlobalCube\System\Autoline\IQD\zeiten\Stempelzeiten_Monteure.imr
TITLE,Stempelzeiten_Monteure.imr
BEGIN SQL
select T1."Resource" as c1,
	   T1."DateIn" as c2,
	   T1."TimeIn" as c3,
	   T1."Sequence" as c4,
	   T1."WIPNumber" as c5,
	   T1."WIPLine" as c6,
	   T1."MultipleCount" as c7,
	   T1."IdleCode" as c8,
	   T1."DateOut" as c9,
	   T1."TimeOut" as c10,
	   T1."WhyClockedOff" as c11,
	   T1."AdjMode" as c12,
	   T1."HoursAllowed" as c13,
	   T1."HoursTaken" as c14,
	   T1."AdjUser" as c15,
	   T1."AdjDate" as c16,
	   T1."AdjTime" as c17,
	   T1."AdjDescription" as c18,
	   T1."ResourceAnalysis" as c19,
	   T1."TerminalID" as c20,
	   T1."ResourceTeam" as c21,
	   T1."State" as c22,
	   T1."CostPerHour" as c23,
	   T1."ResourceShift" as c24,
	   T1."Factor" as c25,
	   T1."BonusTime" as c26,
	   T1."Client_DB" as c27,
	   T2."Code" as c28,
	   T2."NameDescription" as c29,
	   T2."AnalysisCode" as c30,
	   T2."ResignedDate" as c31,
	   T2."Client_DB" as c32,
	   T2."NameDescription" as c33,
	   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 c34,
	   '' as c35,
	   T2."Client_DB" as c36,
	   T3."LocationCode" as c37,
	   T3."Description" as c38,
	   T3."Description" as c39,
	   (CASE WHEN (T4."UserID" IS NOT NULL) THEN ('unproduktiv') ELSE ('produktiv') END) as c40,
	   T5."Code" as c41,
	   T5."Description001" as c42,
	   T5."Description002" as c43,
	   CASE WHEN (T4."UserID" IS NOT NULL) THEN ('unproduktiv') ELSE ('produktiv') END as c44,
	   T2."AnalysisCode" as c45,
	   T6."Code" as c46,
	   T6."Description001" as c47,
	   T6."Description002" as c48,
	   T6."Description003" as c49,
	   CASE WHEN (T1."IdleCode" IN (' ')) THEN ('prod.') WHEN (not T1."IdleCode" IN (' ')) THEN ('unprod.') ELSE null END as c50,
	   T6."Code" || ' - ' || T6."Description002" as c51,
	   (T6."Code" || ' - ' || T6."Description002") as c52,
	   T1."WIPNumber" as c53,
	   '' as c54,
	   T1."DateIn" as c55,
	   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 c56,
	   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 c57,
	   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 c58,
	   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 c59,
	   T1."Resource" || '_' || T1."Client_DB" as c60,
	   T4."UserID" as c61,
	   T4."JobTitle" as c62
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 (od_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" >= TIMESTAMP '2022-01-01 00:00:00.000') and (T2."Code" IS NOT NULL))
order by c1 asc,c2 asc

END SQL
COLUMN,0,Resource
COLUMN,1,Datein
COLUMN,2,Timein
COLUMN,3,Sequence
COLUMN,4,Wipnumber
COLUMN,5,Wipline
COLUMN,6,Multiplecount
COLUMN,7,Idlecode
COLUMN,8,Dateout
COLUMN,9,Timeout
COLUMN,10,Whyclockedoff
COLUMN,11,Adjmode
COLUMN,12,Hoursallowed
COLUMN,13,Hourstaken
COLUMN,14,Adjuser
COLUMN,15,Adjdate
COLUMN,16,Adjtime
COLUMN,17,Adjdescription
COLUMN,18,Resourceanalysis
COLUMN,19,Terminalid
COLUMN,20,Resourceteam
COLUMN,21,State
COLUMN,22,Costperhour
COLUMN,23,Resourceshift
COLUMN,24,Factor
COLUMN,25,Bonustime
COLUMN,26,Client Db
COLUMN,27,Code_Resource
COLUMN,28,Namedescription_Resource
COLUMN,29,Analysiscode_Resource
COLUMN,30,Resigneddate_Resource
COLUMN,31,Client Db_Resource
COLUMN,32,Monteur
COLUMN,33,Hauptbetrieb_ID
COLUMN,34,Hauptbetrieb_Name
COLUMN,35,Standort_ID
COLUMN,36,Locationcode
COLUMN,37,Description
COLUMN,38,Standort_Name
COLUMN,39,Monteur_Gruppe
COLUMN,40,Code_Team
COLUMN,41,Description001_Team
COLUMN,42,Description002_Team
COLUMN,43,produktiv_unproduktiv
COLUMN,44,Monteur_Gruppe_2
COLUMN,45,Code_Idle
COLUMN,46,Description001_Idle
COLUMN,47,Description002_Idle
COLUMN,48,Description003_Idle
COLUMN,49,Activity_Codes_Group1
COLUMN,50,Activity_Codes_Group2
COLUMN,51,Activity_Desc
COLUMN,52,Order Number
COLUMN,53,Kostenstelle
COLUMN,54,Datum
COLUMN,55,prod.
COLUMN,56,unprod.
COLUMN,57,verr Zeit
COLUMN,58,benutzte Zeit
COLUMN,59,Monteur_eindeutig
COLUMN,60,Userid
COLUMN,61,Jobtitle