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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,ARIntelligence
DATASOURCENAME,C:\GlobalCube\SYSTEM\ARIntelligence\IQD\zeit\Time_Clock_Entry.imr
TITLE,Time_Clock_Entry.imr
BEGIN SQL
select c162 as c1,
	   c161 as c2,
	   c160 as c3,
	   c159 as c4,
	   c158 as c5,
	   c157 as c6,
	   c156 as c7,
	   c155 as c8,
	   c154 as c9,
	   c153 as c10,
	   c152 as c11,
	   c151 as c12,
	   c150 as c13,
	   c149 as c14,
	   c148 as c15,
	   c147 as c16,
	   c146 as c17,
	   c145 as c18,
	   c144 as c19,
	   c143 as c20,
	   c142 as c21,
	   c141 as c22,
	   c140 as c23,
	   c139 as c24,
	   c138 as c25,
	   c137 as c26,
	   c136 as c27,
	   c135 as c28,
	   c134 as c29,
	   c133 as c30,
	   c132 as c31,
	   c131 as c32,
	   c130 as c33,
	   c129 as c34,
	   c128 as c35,
	   c127 as c36,
	   c126 as c37,
	   c125 as c38,
	   c124 as c39,
	   c123 as c40,
	   c122 as c41,
	   c121 as c42,
	   c120 as c43,
	   c119 as c44,
	   c114 as c45,
	   c118 as c46,
	   c117 as c47,
	   c116 as c48,
	   c115 as c49,
	   c114 as c50,
	   c113 as c51,
	   c112 as c52,
	   c111 as c53,
	   c110 as c54,
	   c109 as c55,
	   c108 as c56,
	   c107 as c57,
	   c106 as c58,
	   c105 as c59,
	   c106 as c60,
	   c105 as c61,
	   c104 as c62,
	   c103 as c63,
	   '1' as c64,
	   c102 as c65,
	   c101 as c66,
	   c100 as c67,
	   c99 as c68,
	   c98 as c69,
	   c97 as c70,
	   c96 as c71,
	   c95 as c72,
	   c94 as c73,
	   c93 as c74,
	   c92 as c75,
	   XSUM(c92 for c88) as c76,
	   CASE WHEN ((XSUM(c92 for c88)) > 8) THEN (8) ELSE ((XSUM(c92 for c88))) END as c77,
	   XCOUNT(c161 for c88) as c78,
	   (CASE WHEN ((XSUM(c92 for c88)) > 8) THEN (8) ELSE ((XSUM(c92 for c88))) END) / (XCOUNT(c161 for c88)) as c79,
	   c91 as c80,
	   c90 as c81,
	   c89 as c82
from 
(select ((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) || (asciiz(extract(YEAR 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)))),4) || '-' || asciiz(extract(MONTH 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)))),2) || '-' || asciiz(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)))),2))) as c88,
	   T4."Department No_" as c89,
	   T2."Efficiency" as c90,
	   CASE WHEN (T7."ACTIVITY_DESC" IN ('242 - Meistervertretung Werkstatt','244 - Meistervertretung Serviceberater')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c91,
	   (CASE WHEN (T7."ACTIVITY_CODES_GROUP1" = 'prod.') THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T7."ACTIVITY_CODES_GROUP1" = 'W-var. Std.') THEN (((cast_float(T1."Duration")))) ELSE (0) END) - (CASE WHEN (T7."ACTIVITY_DESC" IN ('243 - Servicemobil')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T7."ACTIVITY_DESC" = '319 - Krankheit') THEN (((cast_float(T1."Duration")))) ELSE (0) END) as c92,
	   CASE WHEN (T7."ACTIVITY_DESC" = '319 - Krankheit') THEN (((cast_float(T1."Duration")))) ELSE (0) END as c93,
	   CASE WHEN (T7."ACTIVITY_DESC" IN ('243 - Servicemobil')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c94,
	   (CASE WHEN (T7."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 c95,
	   CASE WHEN (T7."ACTIVITY_CODES_GROUP1" = 'W-fix Std.') THEN (((cast_float(T1."Duration")))) ELSE (0) END as c96,
	   CASE WHEN (T7."ACTIVITY_CODES_GROUP1" = 'W-var. Std.') THEN (((cast_float(T1."Duration")))) ELSE (0) END as c97,
	   CASE WHEN (T7."ACTIVITY_CODES_GROUP1" = 'prod.') THEN (((cast_float(T1."Duration")))) ELSE (0) END as c98,
	   T7."ACTIVITY_CODES_GROUP2" as c99,
	   T7."ACTIVITY_CODES_GROUP1" as c100,
	   T7."ACTIVITY_DESC" as c101,
	   CASE WHEN ((od_left(T4."Department No_",2)) = '10') THEN ('LBS') WHEN ((od_left(T4."Department No_",2)) = '20') THEN ('WLS') ELSE null END as c102,
	   CASE WHEN ((extract(DAY FROM (now()) - ((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))))) <= 93) 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 c103,
	   CASE WHEN (((extract(DAY FROM (now()) - ((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))))) <= 93) and (T5."Bill-to Name" IS NOT NULL)) THEN (T1."Service Order No_" || ' - ' || T5."Bill-to Name") WHEN (((extract(DAY FROM (now()) - ((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))))) <= 93) and (T5."Bill-to Name" IS NULL)) THEN (T1."Service Order No_" || ' - ' || T6."Bill-to Name") ELSE null END as c104,
	   T6."Bill-to Name" as c105,
	   T5."Bill-to Name" as c106,
	   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 c107,
	   (now()) - INTERVAL '001 10:00:00.000' as c108,
	   (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 c109,
	   (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 c110,
	   (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 c111,
	   CASE WHEN ((T4."Leaving Date" < (@CURRENT_DATE)) and (T4."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T4."Group No_ 2") END as c112,
	   (extract(DAY FROM (now()) - T4."Leaving Date")) as c113,
	   T4."Group No_ 2" as c114,
	   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 c115,
	   CASE WHEN (T4."Group No_ 3" IN ('KAR','WER')) THEN ('prod. Personal') WHEN (T4."Group No_ 3" IN ('SON')) THEN ('unprod. Personal') ELSE null END as c116,
	   T4."Leaving Date" as c117,
	   T4."Group No_ 3" as c118,
	   T4."Group No_ 1" as c119,
	   T4."First Name" as c120,
	   T4."Last Name" as c121,
	   T4."Name" as c122,
	   T4."No_" as c123,
	   T3."Description" as c124,
	   T3."Code" as c125,
	   T1."Passed" as c126,
	   T1."Automatic" as c127,
	   CASE WHEN (((cast_float(T1."Efficiency _")) = 0) and (T2."Efficiency" <> 0)) THEN (T2."Efficiency") ELSE ((cast_float(T1."Efficiency _"))) END as c128,
	   T1."Source Code" as c129,
	   T1."Description" as c130,
	   T1."Labor No_" as c131,
	   T1."Branch Code" as c132,
	   T1."Make Code" as c133,
	   T1."Resource Location Code" as c134,
	   T1."Order Location Code" as c135,
	   T1."Considered as Working" as c136,
	   T1."Statistics Group" as c137,
	   T1."Productive" as c138,
	   T1."Creation Timestamp" as c139,
	   T1."Created by User ID" as c140,
	   T1."Cancelation Timestamp" as c141,
	   T1."Canceled by User ID" as c142,
	   T1."Canceled" as c143,
	   T1."Approval Timestamp" as c144,
	   T1."Approved by Employee No_" as c145,
	   T1."Approved" as c146,
	   T1."Labor Standard Time Type" as c147,
	   T1."Service Line No_" as c148,
	   T1."Service Job No_" as c149,
	   T1."Service Order No_" as c150,
	   T1."Leaving" as c151,
	   T1."Linked to Entry No_" as c152,
	   T1."Type" as c153,
	   T1."Task Type Code" as c154,
	   (cast_float(T1."Duration")) as c155,
	   T1."Sorting" as c156,
	   T1."Time" as c157,
	   T1."Date" as c158,
	   T1."Address No_" as c159,
	   T1."Resource No_" as c160,
	   T1."Employee No_" as c161,
	   T1."Entry No_" as c162
from QSS."C:\GlobalCube\System\ARIntelligence\IQD\Zeit\Resource_ims.ims" T2,
	((((("Automag7x"."dbo"."Automag GmbH$Time Clock Entry" T1 left outer join "Automag7x"."dbo"."Automag GmbH$Task Type" T3 on T1."Task Type Code" = T3."Code") left outer join "Automag7x"."dbo"."Automag GmbH$Employee_T" T4 on T1."Employee No_" = T4."No_") left outer join "Automag7x"."dbo"."Automag GmbH$Archived Service Header" T5 on T1."Service Order No_" = T5."No_") left outer join "Automag7x"."dbo"."Automag GmbH$Service Header" T6 on T1."Service Order No_" = T6."No_") left outer join QSS."C:\GlobalCube\System\ARIntelligence\IQD\Zeit\Zuordnung_Task_Type.ims" T7 on T1."Task Type Code" = T7."NO")
where (T1."Resource No_" = T2."No_")
and (((((((T1."Sorting" = 0) and (T1."Canceled" = 0)) and (T1."Date" >= TIMESTAMP '2021-01-01 00:00:00.000')) and ((CASE WHEN (T4."Group No_ 3" IN ('KAR','WER')) THEN ('prod. Personal') WHEN (T4."Group No_ 3" IN ('SON')) THEN ('unprod. Personal') ELSE null END) = 'prod. Personal')) and (not T1."Task Type Code" IN ('312'))) and (T1."Employee No_" <> '0045')) and (not (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) IN ('Nikolaos Moisiadis','Daniele Fecondo','Benjamin Hilla Löwe 8','Dawid Mycek ZAK','Dennis Weist ZAK','Tim Bärmann','Daniel Drechsler','Nicolai von der Recke','Julian Suchomel','Matthias Mühlbauer','Marek Podenas Boetronic','Niclas Stelzner','Maximilian Stiller','Michael Adden','Ruggero Bergamo')))
) D1
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
COLUMN,52,Datum
COLUMN,53,Monatserster
COLUMN,54,Monatsletzter
COLUMN,55,Heute
COLUMN,56,Datum Tagesbericht
COLUMN,57,Bill-to Name_Archieved_Service_Header
COLUMN,58,Bill-to Name_Service_Header
COLUMN,59,Kunde
COLUMN,60,Kunde_oA
COLUMN,61,Auftrag_Kunde
COLUMN,62,Datum Monteurlisten
COLUMN,63,Hauptbetrieb
COLUMN,64,Standort
COLUMN,65,Activity Desc
COLUMN,66,Activity Codes Group1
COLUMN,67,Activity Codes Group2
COLUMN,68,prod.
COLUMN,69,unprod.
COLUMN,70,Abw.
COLUMN,71,produktiv_für_Berechnung_LG
COLUMN,72,Servicemobil
COLUMN,73,krank
COLUMN,74,Anwesenheit bereinigt_für_erzielte_Anwesenheit
COLUMN,75,Summe_Anw_bereinigt_Tag
COLUMN,76,erzíelte Anwesenheitsstunden bereinigt
COLUMN,77,Anzahl_Datensätze
COLUMN,78,erzielte Anwesenheiststunden für Berechnung Soll_Leistung
COLUMN,79,Meistervertr.
COLUMN,80,Efficiency_Resource_ims
COLUMN,81,Department No_Employee_T