C11/System/ARIntelligence/IQD/Zeit/Stempelzeiten_Monteur_2_Ryma_Terminpünktlichkeit.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Zeit\Stempelzeiten_Monteur_2_Ryma_Terminpünktlichkeit.imr
TITLE,Stempelzeiten_Monteur_2_Ryma_Terminpünktlichkeit
BEGIN SQL
select c1 as c1,
	   c2 as c2,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   c6 as c6,
	   c7 as c7,
	   c8 as c8,
	   c9 as c9,
	   c10 as c10,
	   c11 as c11,
	   c12 as c12,
	   c13 as c13,
	   c14 as c14,
	   c15 as c15,
	   c16 as c16,
	   c17 as c17,
	   c18 as c18,
	   c19 as c19,
	   c20 as c20,
	   c21 as c21,
	   c22 as c22,
	   c23 as c23,
	   c24 as c24,
	   c25 as c25,
	   c26 as c26,
	   c27 as c27,
	   c28 as c28,
	   c29 as c29,
	   c30 as c30,
	   c31 as c31,
	   c32 as c32,
	   c33 as c33,
	   c34 as c34,
	   c35 as c35,
	   c36 as c36,
	   c37 as c37,
	   c38 as c38,
	   c39 as c39,
	   c40 as c40,
	   c41 as c41,
	   c42 as c42,
	   c43 as c43,
	   RSUM(c43) as c44,
	   c45 as c45,
	   c46 as c46,
	   c47 as c47,
	   c48 as c48,
	   c49 as c49,
	   c50 as c50,
	   c51 as c51,
	   c52 as c52,
	   c53 as c53,
	   c54 as c54,
	   c55 as c55,
	   c56 as c56,
	   c57 as c57,
	   c58 as c58,
	   c59 as c59,
	   c60 as c60,
	   c61 as c61,
	   c62 as c62,
	   c63 as c63,
	   c64 as c64,
	   c65 as c65,
	   c66 as c66,
	   c67 as c67,
	   c68 as c68,
	   c69 as c69,
	   c70 as c70,
	   c71 as c71,
	   c72 as c72,
	   c73 as c73,
	   c74 as c74,
	   c75 as c75,
	   c76 as c76,
	   c77 as c77,
	   c78 as c78,
	   c79 as c79,
	   c80 as c80,
	   c81 as c81,
	   c82 as c82,
	   c83 as c83
from 
(select c162 as c1,
	   c123 as c2,
	   c122 as c3,
	   c161 as c4,
	   c160 as c5,
	   c159 as c6,
	   c158 as c7,
	   c157 as c8,
	   c156 as c9,
	   c155 as c10,
	   c154 as c11,
	   c153 as c12,
	   c152 as c13,
	   c111 as c14,
	   c151 as c15,
	   c150 as c16,
	   c149 as c17,
	   c148 as c18,
	   c147 as c19,
	   c146 as c20,
	   c145 as c21,
	   c144 as c22,
	   c143 as c23,
	   c142 as c24,
	   c141 as c25,
	   c140 as c26,
	   c139 as c27,
	   c138 as c28,
	   c137 as c29,
	   c136 as c30,
	   c135 as c31,
	   c134 as c32,
	   c133 as c33,
	   c132 as c34,
	   c131 as c35,
	   c130 as c36,
	   c129 as c37,
	   c128 as c38,
	   c127 as c39,
	   c126 as c40,
	   c125 as c41,
	   c124 as c42,
	   c121 as c43,
	   c120 as c45,
	   c119 as c46,
	   c118 as c47,
	   '1' as c48,
	   c117 as c49,
	   c116 as c50,
	   c115 as c51,
	   c114 as c52,
	   c113 as c53,
	   c112 as c54,
	   c111 as c55,
	   c110 as c56,
	   c109 as c57,
	   c107 as c58,
	   c108 as c59,
	   c107 as c60,
	   c106 as c61,
	   c105 as c62,
	   c104 as c63,
	   c103 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,
	   c91 as c76,
	   c90 as c77,
	   1 as c78,
	   XCOUNT(c162 for c87) as c79,
	   1 / (XCOUNT(c162 for c87)) as c80,
	   c89 as c81,
	   (c89) / (XCOUNT(c162 for c87)) as c82,
	   c88 as c83
from 
(select (T1."Order No_" || (T3."First Name" || ' ' || T3."Last Name")) as c87,
	   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 c88,
	   CASE WHEN (T4."Completion Date" <= T4."Pickup Date") THEN (1) ELSE (0) END as c89,
	   T4."Completion Time" as c90,
	   T4."Completion Date" as c91,
	   T4."Pickup Time" as c92,
	   T4."Pickup Date" as c93,
	   T5."Bill-to Name" as c94,
	   CASE WHEN (((extract(DAY FROM (now()) - (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))) <= 63) and (T4."Bill-to Name" IS NOT NULL)) THEN (T1."Order No_" || ' - ' || T4."Bill-to Name") WHEN (((extract(DAY FROM (now()) - (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))) <= 63) and (T4."Bill-to Name" IS NULL)) THEN (T1."Order No_" || ' - ' || T5."Bill-to Name") ELSE null END as c95,
	   CASE WHEN ((extract(DAY FROM (now()) - (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 ((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 c96,
	   T4."Bill-to Name" as c97,
	   T3."Department No_" as c98,
	   T3."Employment Date" as c99,
	   CASE WHEN (((now()) - INTERVAL '001 10:00:00.000') BETWEEN ((cdatetime((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 (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((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 ((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 c100,
	   (now()) - INTERVAL '001 10:00:00.000' as c101,
	   (cdatetime(lastday(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 c102,
	   (cdatetime((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 (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 c103,
	   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 c104,
	   CASE WHEN ((T3."Leaving Date" <= TIMESTAMP '2016-01-31 00:00:00.000') and (T3."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 2") END as c105,
	   (extract(DAY FROM (now()) - T3."Leaving Date")) as c106,
	   T3."Group No_ 2" as c107,
	   T3."Group No_ 3" as c108,
	   T3."Group No_ 1" as c109,
	   T3."Leaving Date" as c110,
	   T1."Order No_" as c111,
	   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 c112,
	   T3."First Name" || ' ' || T3."Last Name" as c113,
	   T3."First Name" as c114,
	   T3."Last Name" as c115,
	   T3."No_" as c116,
	   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 c117,
	   T2."Task Statistic Group" as c118,
	   T2."Description" as c119,
	   T2."Code" as c120,
	   (cast_float(T1."Duration")) as c121,
	   T1."Date" as c122,
	   T1."Employee No_" as c123,
	   T1."Order Line No_" as c124,
	   T1."Labor No_" as c125,
	   T1."Labor Standard Time Type" as c126,
	   T1."Resource Efficiency %" as c127,
	   T1."Leave" as c128,
	   T1."Location Code" as c129,
	   T1."Tested" as c130,
	   T1."Resource Group No_" as c131,
	   T1."Service Advisor No_" as c132,
	   T1."Posting Time" as c133,
	   T1."Correction Mode" as c134,
	   T1."Ledger Entry Origin" as c135,
	   T1."Applied-to Entry No_" as c136,
	   T1."Subject to Approval" as c137,
	   T1."Automatic Posting" as c138,
	   T1."Approved" as c139,
	   T1."Approved by User ID" as c140,
	   T1."Branch Code" as c141,
	   T1."Time Acquisition Posted" as c142,
	   T1."Duration" as c143,
	   T1."Ending Time" as c144,
	   T1."Starting Time" as c145,
	   T1."Corrected" as c146,
	   T1."Task Ledger Entry No_" as c147,
	   T1."Link No_" as c148,
	   T1."Task Type Code" as c149,
	   T1."Closed" as c150,
	   T1."Service Job No_" as c151,
	   T1."Posting Date" as c152,
	   T1."Sorting" as c153,
	   T1."Reason Code" as c154,
	   T1."User ID" as c155,
	   T1."Make Code" as c156,
	   T1."Department Code" as c157,
	   T1."Terminal Code" as c158,
	   T1."Cause of Absence Code" as c159,
	   T1."Begin_End" as c160,
	   T1."Time Value" as c161,
	   T1."Entry No_" as c162
from (((("DMS1"."dbo"."Automag GmbH$Task Acquisition Ledger Entry" T1 left outer join "DMS1"."dbo"."Automag GmbH$Task Type" T2 on T1."Task Type Code" = T2."Code") left outer join "DMS1"."dbo"."Automag GmbH$Employee_T" T3 on T1."Employee No_" = T3."No_") left outer join "DMS1"."dbo"."Automag GmbH$Archived Service Header" T4 on T1."Order No_" = T4."No_") left outer join "DMS1"."dbo"."Automag GmbH$Service Header" T5 on T5."No_" = T1."Order No_")
where ((((((((T1."Sorting" = 1) and (T1."Corrected" = 0)) and ((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) >= T3."Employment Date")) and ((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) >= TIMESTAMP '2019-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 (T1."Order No_" <> ' ')) and (not T1."Employee No_" IN ('0045'))) and (not (T3."First Name" || ' ' || T3."Last Name") IN ('Sebastian Eggert','Krzysztof Sowada','Jacek Skuballa','Sebastian Bogner','Sasa Savic','Jawed','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','Maximilian Stiller','Niclas Stelzner','Ruggero Bergamo','Michael Adden')))
) D1
order by c2 asc,c14 asc,c3 asc
) D3

END SQL
COLUMN,0,Entry No
COLUMN,1,Employee No
COLUMN,2,Date
COLUMN,3,Time Value
COLUMN,4,Begin End
COLUMN,5,Cause Of Absence Code
COLUMN,6,Terminal Code
COLUMN,7,Department Code_ori
COLUMN,8,Make Code
COLUMN,9,User Id
COLUMN,10,Reason Code
COLUMN,11,Sorting
COLUMN,12,Posting Date
COLUMN,13,Order No
COLUMN,14,Service Job No
COLUMN,15,Closed
COLUMN,16,Task Type Code
COLUMN,17,Link No
COLUMN,18,Task Ledger Entry No
COLUMN,19,Corrected
COLUMN,20,Starting Time
COLUMN,21,Ending Time
COLUMN,22,Duration
COLUMN,23,Time Acquisition Posted
COLUMN,24,Branch Code
COLUMN,25,Approved By User Id
COLUMN,26,Approved
COLUMN,27,Automatic Posting
COLUMN,28,Subject To Approval
COLUMN,29,Applied-to Entry No
COLUMN,30,Ledger Entry Origin
COLUMN,31,Correction Mode
COLUMN,32,Posting Time
COLUMN,33,Service Advisor No
COLUMN,34,Resource Group No
COLUMN,35,Tested
COLUMN,36,Location Code
COLUMN,37,Leave
COLUMN,38,Resource Efficiency %
COLUMN,39,Labor Standard Time Type
COLUMN,40,Labor No
COLUMN,41,Order Line No
COLUMN,42,Zeitdauer
COLUMN,43,Summe  (Zeitdauer) Nr.1
COLUMN,44,Code
COLUMN,45,Description_Task_Type
COLUMN,46,Task Statistic Group
COLUMN,47,Hauptbetrieb
COLUMN,48,Standort
COLUMN,49,No
COLUMN,50,Last Name
COLUMN,51,First Name
COLUMN,52,Monteur
COLUMN,53,Datum_ori
COLUMN,54,Order Number
COLUMN,55,Leaving Date
COLUMN,56,Group No 1
COLUMN,57,Group No 2
COLUMN,58,Group No 3
COLUMN,59,Monteur_Gruppe_ori
COLUMN,60,Tage Heute Leaving Date
COLUMN,61,Monteur_Gruppe
COLUMN,62,produktiv/unproduktiv
COLUMN,63,Monatserster
COLUMN,64,Monatsletzter
COLUMN,65,Heute
COLUMN,66,Datum Tagesbericht
COLUMN,67,Employment Date
COLUMN,68,Department No
COLUMN,69,Kunde
COLUMN,70,Datum Monteurlisten
COLUMN,71,Auftrag_Kunde
COLUMN,72,Kunde_oA
COLUMN,73,Pickup Date
COLUMN,74,Pickup Time
COLUMN,75,Completion Date
COLUMN,76,Completion Time
COLUMN,77,DG_1
COLUMN,78,DG_2
COLUMN,79,Durchgänge
COLUMN,80,Auftragspünktlichkeit
COLUMN,81,Durchgänge pünktlich
COLUMN,82,Datum