COGNOS QUERY
STRUCTURE,1,1
DATABASE,ARIntelligence
DATASOURCENAME,C:\GlobalCube\SYSTEM\ARIntelligence\IQD\zeit\Time_Clock_Entry_Terminpuenktlichkeit.imr
TITLE,Time_Clock_Entry_Terminpuenktlichkeit.imr
BEGIN SQL
select c147 as c1,
	   c146 as c2,
	   c145 as c3,
	   c144 as c4,
	   c97 as c5,
	   c143 as c6,
	   c142 as c7,
	   c141 as c8,
	   c140 as c9,
	   c139 as c10,
	   c138 as c11,
	   c137 as c12,
	   c136 as c13,
	   c135 as c14,
	   c134 as c15,
	   c133 as c16,
	   c132 as c17,
	   c131 as c18,
	   c130 as c19,
	   c129 as c20,
	   c128 as c21,
	   c127 as c22,
	   c126 as c23,
	   c125 as c24,
	   c124 as c25,
	   c123 as c26,
	   c122 as c27,
	   c121 as c28,
	   c120 as c29,
	   c119 as c30,
	   c118 as c31,
	   c117 as c32,
	   c116 as c33,
	   c115 as c34,
	   c114 as c35,
	   c113 as c36,
	   c112 as c37,
	   c111 as c38,
	   c110 as c39,
	   c109 as c40,
	   c108 as c41,
	   c107 as c42,
	   c106 as c43,
	   c105 as c44,
	   c100 as c45,
	   c104 as c46,
	   c103 as c47,
	   c102 as c48,
	   c101 as c49,
	   c100 as c50,
	   c99 as c51,
	   c98 as c52,
	   c97 as c53,
	   c96 as c54,
	   c95 as c55,
	   c94 as c56,
	   c93 as c57,
	   c92 as c58,
	   c91 as c59,
	   c92 as c60,
	   c91 as c61,
	   c90 as c62,
	   c89 as c63,
	   '1' as c64,
	   c88 as c65,
	   c87 as c66,
	   c86 as c67,
	   c85 as c68,
	   c84 as c69,
	   1 as c70,
	   XCOUNT(c147 for c80) as c71,
	   1 / (XCOUNT(c147 for c80)) as c72,
	   c83 as c73,
	   (c83) / (XCOUNT(c147 for c80)) as c74,
	   c82 as c75,
	   c81 as c76
from 
(select (T1."Service Order No_" || (CASE WHEN (T3."Last Name" = 'Silberbauer') THEN ('Leonhard Silberbauer') WHEN (T3."Last Name" = 'Liebick') THEN ('Lukas Jonathan Josef Liebick') ELSE (T3."First Name" || ' ' || T3."Last Name") END)) as c80,
	   T3."Department No_" as c81,
	   (cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T4."Posting Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T4."Posting Date") END)) as c82,
	   CASE WHEN (T4."Completion Date" <= T4."Pickup Date") THEN (1) ELSE (0) END as c83,
	   T4."Completion Time" as c84,
	   T4."Completion Date" as c85,
	   T4."Pickup Time" as c86,
	   T4."Pickup Date" as c87,
	   CASE WHEN ((od_left(T3."Department No_",2)) = '10') THEN ('LBS') WHEN ((od_left(T3."Department No_",2)) = '20') THEN ('WLS') ELSE null END as c88,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Date")) <= 93) THEN (T1."Date") ELSE null END as c89,
	   CASE WHEN (((extract(DAY FROM (now()) - T1."Date")) <= 93) and (T4."Bill-to Name" IS NOT NULL)) THEN (T1."Service Order No_" || ' - ' || T4."Bill-to Name") WHEN (((extract(DAY FROM (now()) - T1."Date")) <= 93) and (T4."Bill-to Name" IS NULL)) THEN (T1."Service Order No_" || ' - ' || T5."Bill-to Name") ELSE null END as c90,
	   T5."Bill-to Name" as c91,
	   T4."Bill-to Name" as c92,
	   CASE WHEN (((now()) - INTERVAL '001 10:00:00.000') BETWEEN ((cdatetime(T1."Date" - cinterval(extract(DAY FROM T1."Date") - 1)))) AND ((cdatetime(lastday(cdate(T1."Date")))))) THEN (T1."Date") ELSE null END as c93,
	   (now()) - INTERVAL '001 10:00:00.000' as c94,
	   (cdatetime(lastday(cdate(T1."Date")))) as c95,
	   (cdatetime(T1."Date" - cinterval(extract(DAY FROM T1."Date") - 1))) as c96,
	   T1."Date" as c97,
	   CASE WHEN ((T3."Leaving Date" < (@CURRENT_DATE)) and (T3."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 2") END as c98,
	   (extract(DAY FROM (now()) - T3."Leaving Date")) as c99,
	   T3."Group No_ 2" as c100,
	   CASE WHEN (T3."Last Name" = 'Silberbauer') THEN ('Leonhard Silberbauer') WHEN (T3."Last Name" = 'Liebick') THEN ('Lukas Jonathan Josef Liebick') ELSE (T3."First Name" || ' ' || T3."Last Name") END as c101,
	   CASE WHEN (T3."Group No_ 3" IN ('KAR','WER')) THEN ('prod. Personal') WHEN (T3."Group No_ 3" IN ('SON')) THEN ('unprod. Personal') ELSE null END as c102,
	   T3."Leaving Date" as c103,
	   T3."Group No_ 3" as c104,
	   T3."Group No_ 1" as c105,
	   T3."First Name" as c106,
	   T3."Last Name" as c107,
	   T3."Name" as c108,
	   T3."No_" as c109,
	   T2."Description" as c110,
	   T2."Code" as c111,
	   T1."Passed" as c112,
	   T1."Automatic" as c113,
	   (cast_float(T1."Efficiency _")) as c114,
	   T1."Source Code" as c115,
	   T1."Description" as c116,
	   T1."Labor No_" as c117,
	   T1."Branch Code" as c118,
	   T1."Make Code" as c119,
	   T1."Resource Location Code" as c120,
	   T1."Order Location Code" as c121,
	   T1."Considered as Working" as c122,
	   T1."Statistics Group" as c123,
	   T1."Productive" as c124,
	   T1."Creation Timestamp" as c125,
	   T1."Created by User ID" as c126,
	   T1."Cancelation Timestamp" as c127,
	   T1."Canceled by User ID" as c128,
	   T1."Canceled" as c129,
	   T1."Approval Timestamp" as c130,
	   T1."Approved by Employee No_" as c131,
	   T1."Approved" as c132,
	   T1."Labor Standard Time Type" as c133,
	   T1."Service Line No_" as c134,
	   T1."Service Job No_" as c135,
	   T1."Service Order No_" as c136,
	   T1."Leaving" as c137,
	   T1."Linked to Entry No_" as c138,
	   T1."Type" as c139,
	   T1."Task Type Code" as c140,
	   (cast_float(T1."Duration")) as c141,
	   T1."Sorting" as c142,
	   T1."Time" as c143,
	   T1."Address No_" as c144,
	   T1."Resource No_" as c145,
	   T1."Employee No_" as c146,
	   T1."Entry No_" as c147
from (((("Automag7x"."dbo"."Automag GmbH$Time Clock Entry" T1 left outer join "Automag7x"."dbo"."Automag GmbH$Task Type" T2 on T1."Task Type Code" = T2."Code") left outer join "Automag7x"."dbo"."Automag GmbH$Employee_T" T3 on T1."Employee No_" = T3."No_") left outer join "Automag7x"."dbo"."Automag GmbH$Archived Service Header" T4 on T1."Service Order No_" = T4."No_") left outer join "Automag7x"."dbo"."Automag GmbH$Service Header" T5 on T1."Service Order No_" = T5."No_")
where (((((((T1."Sorting" = 0) and (T1."Canceled" = 0)) and (T1."Date" >= TIMESTAMP '2021-01-01 00:00:00.000')) and ((CASE WHEN (T3."Group No_ 3" IN ('KAR','WER')) THEN ('prod. Personal') WHEN (T3."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 (T3."Last Name" = 'Silberbauer') THEN ('Leonhard Silberbauer') WHEN (T3."Last Name" = 'Liebick') THEN ('Lukas Jonathan Josef Liebick') ELSE (T3."First Name" || ' ' || T3."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_ori
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,Pickup Date
COLUMN,66,Pickup Time
COLUMN,67,Completion Date
COLUMN,68,Completion Time
COLUMN,69,DG_1
COLUMN,70,DG_2
COLUMN,71,Durchgänge
COLUMN,72,Auftragspünktlichkeit
COLUMN,73,Durchgänge pünktlich
COLUMN,74,Datum
COLUMN,75,Department No_Employee_T