C11/System/ARI/IQD/Zeit/Time_Clock_Entry.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,ARIntelligence
DATASOURCENAME,C:\GlobalCube\System\ARI\IQD\Zeit\Time_Clock_Entry.imr
TITLE,Time_Clock_Entry.imr
BEGIN SQL
select T1."Entry No_" as c1,
	   T1."Employee No_" as c2,
	   T1."Resource No_" as c3,
	   T1."Address No_" as c4,
	   T1."Date" as c5,
	   T1."Time" as c6,
	   T1."Sorting" as c7,
	   (cast_float(T1."Duration")) as c8,
	   T1."Task Type Code" as c9,
	   T1."Type" as c10,
	   T1."Linked to Entry No_" as c11,
	   T1."Leaving" as c12,
	   T1."Service Order No_" as c13,
	   T1."Service Job No_" as c14,
	   T1."Service Line No_" as c15,
	   T1."Labor Standard Time Type" as c16,
	   T1."Approved" as c17,
	   T1."Approved by Employee No_" as c18,
	   T1."Approval Timestamp" as c19,
	   T1."Canceled" as c20,
	   T1."Canceled by User ID" as c21,
	   T1."Cancelation Timestamp" as c22,
	   T1."Created by User ID" as c23,
	   T1."Creation Timestamp" as c24,
	   T1."Productive" as c25,
	   T1."Statistics Group" as c26,
	   T1."Considered as Working" as c27,
	   T1."Order Location Code" as c28,
	   T1."Resource Location Code" as c29,
	   T1."Make Code" as c30,
	   T1."Branch Code" as c31,
	   T1."Labor No_" as c32,
	   T1."Description" as c33,
	   T1."Source Code" as c34,
	   CASE WHEN (((cast_float(T1."Efficiency _")) = 0) and (T2."Efficiency" <> 0)) THEN (T2."Efficiency") ELSE ((cast_float(T1."Efficiency _"))) END as c35,
	   T1."Automatic" as c36,
	   T1."Passed" as c37,
	   T3."CODE" as c38,
	   T3."DESCRIPTION" as c39,
	   T4."No_" as c40,
	   T4."Name" as c41,
	   T4."Last Name" as c42,
	   T4."First Name" as c43,
	   T4."Group No_ 1" as c44,
	   T4."Group No_ 2" as c45,
	   T4."Group No_ 3" as c46,
	   T4."Leaving Date" as c47,
	   CASE WHEN (T5."Function Code" IN ('AZUBI','MECH','SERVHILF','SERVTECH','WAGENPFLEG','WERKMEI')) THEN ('prod. Personal') ELSE ('unprod. Personal') END as c48,
	   CASE WHEN (T4."Last Name" = 'Silberbauer') THEN ('Leonhard Silberbauer') WHEN (T4."Last Name" = 'Liebick') THEN ('Lukas Jonathan Josef Liebick') ELSE (T4."First Name" || ' ' || T4."Last Name") END as c49,
	   T5."Function Code" as c50,
	   (extract(DAY FROM (now()) - T4."Leaving Date")) as c51,
	   CASE WHEN ((T4."Leaving Date" < (now())) and (T4."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T5."Function Code") END as c52,
	   (cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Date") END)) as c53,
	   (cdatetime(((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Date") END))) - cinterval(extract(DAY FROM ((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Date") END)))) - 1))) as c54,
	   (cdatetime(lastday(cdate(((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Date") END))))))) as c55,
	   (now()) - INTERVAL '001 10:00:00.000' as c56,
	   CASE WHEN (((now()) - INTERVAL '001 10:00:00.000') BETWEEN ((cdatetime(((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Date") END))) - cinterval(extract(DAY FROM ((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Date") END)))) - 1)))) AND ((cdatetime(lastday(cdate(((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Date") END))))))))) THEN (((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Date") END)))) ELSE null END as c57,
	   '1' as c58,
	   (od_left(T4."Department No_",2)) as c59,
	   CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.') THEN (((cast_float(T1."Duration")))) ELSE (0) END as c60,
	   CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'W-var. Std.') THEN (((cast_float(T1."Duration")))) ELSE (0) END as c61,
	   CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'W-fix Std.') THEN (((cast_float(T1."Duration")))) ELSE (0) END as c62,
	   (CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.') THEN (((cast_float(T1."Duration")))) ELSE (0) END) * ((CASE WHEN (((cast_float(T1."Efficiency _")) = 0) and (T2."Efficiency" <> 0)) THEN (T2."Efficiency") ELSE ((cast_float(T1."Efficiency _"))) END) / 100) as c63,
	   CASE WHEN (T3."ACTIVITY_DESC" IN ('243 - Servicemobil')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c64,
	   CASE WHEN (T3."ACTIVITY_DESC" = '319 - Krankheit') THEN (((cast_float(T1."Duration")))) ELSE (0) END as c65,
	   CASE WHEN (T3."ACTIVITY_DESC" IN ('242 - Meistervertretung Werkstatt','244 - Meistervertretung Serviceberater')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c66,
	   T3."ACTIVITY_DESC" as c67,
	   (od_left(T4."Department No_",2)) as c68,
	   (CASE WHEN (T5."Function Code" IN ('AZUBI','MECH','SERVHILF','SERVTECH','WAGENPFLEG','WERKMEI')) THEN ('prod. Personal') ELSE ('unprod. Personal') END) as c69,
	   T4."Department No_" as c70,
	   T1."Service Order No_" as c71,
	   T5."Task Type Group" as c72,
	   CASE WHEN (T3."ACTIVITY_DESC" = '111 - Auftrag extern AW') THEN ((CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.') THEN (((cast_float(T1."Duration")))) ELSE (0) END)) ELSE (0) END as c73,
	   CASE WHEN (T3."ACTIVITY_DESC" = '150 - Auftrag intern AW') THEN ((CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.') THEN (((cast_float(T1."Duration")))) ELSE (0) END)) ELSE (0) END as c74,
	   T5."Home Page 2" as c75,
	   (asciiz(extract(YEAR FROM T5."Employment Date"),4) || '-' || asciiz(extract(MONTH FROM T5."Employment Date"),2) || '-' || asciiz(extract(DAY FROM T5."Employment Date"),2)) as c76
from QSS."C:\GlobalCube\System\ARI\IQD\Zeit\Resource_ims.ims" T2,
	((("Gottstein7x"."dbo"."AH Gottstein$Time Clock Entry" T1 left outer join QSS."C:\GlobalCube\System\ARI\IQD\Zeit\Zuordnung_Task_Type.ims" T3 on T3."CODE" = T1."Task Type Code") left outer join "Gottstein7x"."dbo"."AH Gottstein$Employee_T" T4 on T1."Employee No_" = T4."No_") left outer join "Gottstein7x"."dbo"."AH Gottstein$Employee" T5 on T1."Resource No_" = T5."No_")
where (T1."Resource No_" = T2."No_")
and (((((T1."Sorting" = 0) and (T1."Canceled" = 0)) and (T1."Date" >= TIMESTAMP '2022-01-01 00:00:00.000')) and ((CASE WHEN (T5."Function Code" IN ('AZUBI','MECH','SERVHILF','SERVTECH','WAGENPFLEG','WERKMEI')) THEN ('prod. Personal') ELSE ('unprod. Personal') END) IN ('prod. Personal','unprod. Personal'))) and (not T3."CODE" IN ('312')))
order by c3 asc,c5 asc

END SQL
COLUMN,0,Entry No
COLUMN,1,Employee No
COLUMN,2,Resource No
COLUMN,3,Address No
COLUMN,4,Date
COLUMN,5,Time
COLUMN,6,Sorting
COLUMN,7,Duration
COLUMN,8,Task Type Code
COLUMN,9,Type
COLUMN,10,Linked To Entry No
COLUMN,11,Leaving
COLUMN,12,Service Order No
COLUMN,13,Service Job No
COLUMN,14,Service Line No
COLUMN,15,Labor Standard Time Type
COLUMN,16,Approved
COLUMN,17,Approved By Employee No
COLUMN,18,Approval Timestamp
COLUMN,19,Canceled
COLUMN,20,Canceled By User Id
COLUMN,21,Cancelation Timestamp
COLUMN,22,Created By User Id
COLUMN,23,Creation Timestamp
COLUMN,24,Productive
COLUMN,25,Statistics Group
COLUMN,26,Considered As Working
COLUMN,27,Order Location Code
COLUMN,28,Resource Location Code
COLUMN,29,Make Code
COLUMN,30,Branch Code
COLUMN,31,Labor No
COLUMN,32,Description
COLUMN,33,Source Code
COLUMN,34,Efficiency
COLUMN,35,Automatic
COLUMN,36,Passed
COLUMN,37,Code_Task_Type
COLUMN,38,Description_Task_Type
COLUMN,39,No_Employee_T
COLUMN,40,Name_Employee_T
COLUMN,41,Last Name_Employee_T
COLUMN,42,First Name_Employee_T
COLUMN,43,Group No 1_Employee_T
COLUMN,44,Group No 2_Employee_T
COLUMN,45,Group No 3_Employee_T
COLUMN,46,Leaving Date_Employee_T
COLUMN,47,produktiv/unproduktiv
COLUMN,48,Monteur
COLUMN,49,Monteur_Gruppe_ori
COLUMN,50,Tage Heute Leaving Date
COLUMN,51,Monteur_Gruppe_2
COLUMN,52,Datum
COLUMN,53,Monatserster
COLUMN,54,Monatsletzter
COLUMN,55,Heute
COLUMN,56,Datum Tagesbericht
COLUMN,57,Hauptbetrieb_ID
COLUMN,58,Standort
COLUMN,59,prod.
COLUMN,60,unprod.
COLUMN,61,Abw.
COLUMN,62,produktiv_für_Berechnung_LG
COLUMN,63,Servicemobil
COLUMN,64,krank
COLUMN,65,Meistervertr.
COLUMN,66,Activity_Desc
COLUMN,67,Standort_ID
COLUMN,68,Monteur_Gruppe
COLUMN,69,Department No
COLUMN,70,Order Number
COLUMN,71,Task Type Group
COLUMN,72,Extern
COLUMN,73,Intern
COLUMN,74,Home Page 2
COLUMN,75,Employment Date