COGNOS QUERY STRUCTURE,1,1 DATABASE,Navision2 DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Zeit\Stempelzeiten_Monteur_2_Ryma_Anw_bereinigt.imr TITLE,Stempelzeiten_Monteur_2_Ryma_Anw_bereinigt 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, c84 as c84, c85 as c85, c86 as c86, c87 as c87, c88 as c88, c89 as c89, c90 as c90, c91 as c91, c92 as c92, c93 as c93, c94 as c94, c95 as c95, c96 as c96, c97 as c97, c98 as c98, c99 as c99 from (select c194 as c1, c155 as c2, c154 as c3, c193 as c4, c192 as c5, c191 as c6, c190 as c7, c189 as c8, c188 as c9, c187 as c10, c186 as c11, c185 as c12, c184 as c13, c130 as c14, c183 as c15, c182 as c16, c181 as c17, c180 as c18, c179 as c19, c178 as c20, c177 as c21, c176 as c22, c175 as c23, c174 as c24, c173 as c25, c172 as c26, c171 as c27, c170 as c28, c169 as c29, c168 as c30, c167 as c31, c166 as c32, c165 as c33, c164 as c34, c163 as c35, c162 as c36, c161 as c37, c160 as c38, c159 as c39, c158 as c40, c157 as c41, c156 as c42, c153 as c43, c152 as c45, c151 as c46, c150 as c47, c149 as c48, c148 as c49, c147 as c50, c146 as c51, c145 as c52, c144 as c53, c143 as c54, c142 as c55, c141 as c56, c140 as c57, 0 as c58, c139 as c59, c138 as c60, 0 as c61, c137 as c62, c136 as c63, '1' as c64, c135 as c65, c134 as c66, c133 as c67, c132 as c68, c108 as c69, c131 as c70, c109 as c71, c130 as c72, c129 as c73, c128 as c74, c126 as c75, c127 as c76, c126 as c77, c125 as c78, c124 as c79, c123 as c80, c122 as c81, c121 as c82, c120 as c83, c119 as c84, c118 as c85, c117 as c86, c116 as c87, c115 as c88, c114 as c89, c113 as c90, c112 as c91, c111 as c92, c110 as c93, XSUM(c110 for c105) as c94, CASE WHEN ((XSUM(c110 for c105)) > 8) THEN (8) ELSE ((XSUM(c110 for c105))) END as c95, XCOUNT(c155 for c105) as c96, CASE WHEN (((c108) = 'Ioannis Panos') and ((c109) BETWEEN TIMESTAMP '2019-06-01 00:00:00.000' AND TIMESTAMP '2019-06-30 00:00:00.000')) THEN (0) ELSE ((CASE WHEN ((XSUM(c110 for c105)) > 8) THEN (8) ELSE ((XSUM(c110 for c105))) END) / (XCOUNT(c155 for c105))) END as c97, c107 as c98, c106 as c99 from (select ((T3."First Name" || ' ' || T3."Last Name") || (asciiz(extract(YEAR 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)),4) || '-' || asciiz(extract(MONTH 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)),2) || '-' || asciiz(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)),2))) as c105, CASE WHEN (T2."Code" IN ('319')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c106, CASE WHEN (T2."Code" IN ('241')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c107, T3."First Name" || ' ' || T3."Last Name" as c108, 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, ((CASE WHEN (T2."Code" IN ('111')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + 0 + (CASE WHEN (T2."Code" IN ('150')) THEN (((cast_float(T1."Duration")))) ELSE (0) END)) + ((CASE WHEN (T2."Code" IN ('114')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('211','212')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('242','244')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('112','113')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('216')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('331')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('336')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('245')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('243')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('200')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('241')) THEN (((cast_float(T1."Duration")))) ELSE (0) END)) - (CASE WHEN (T2."Code" IN ('243')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('319')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) as c110, ((CASE WHEN (T2."Code" IN ('111')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + 0 + (CASE WHEN (T2."Code" IN ('150')) THEN (((cast_float(T1."Duration")))) ELSE (0) END)) * (((cast_float(T1."Resource Efficiency %"))) / 100) as c111, T5."Bill-to Name" as c112, 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) 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))) <= 93) and (T4."Bill-to Name" IS NULL)) THEN (T1."Order No_" || ' - ' || T5."Bill-to Name") ELSE null END as c113, 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 c114, T4."Bill-to Name" as c115, T3."Department No_" as c116, T3."Employment Date" as c117, (CASE WHEN (T2."Code" IN ('332')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('319')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) as c118, 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 c119, (now()) - INTERVAL '001 10:00:00.000' as c120, (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 c121, (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 c122, 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 c123, 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 c124, (extract(DAY FROM (now()) - T3."Leaving Date")) as c125, T3."Group No_ 2" as c126, T3."Group No_ 3" as c127, T3."Group No_ 1" as c128, T3."Leaving Date" as c129, T1."Order No_" as c130, (CASE WHEN (T2."Code" IN ('114')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('211','212')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('242','244')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('112','113')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('216')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('331')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('336')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('245')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('243')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('200')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('241')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) as c131, T3."First Name" as c132, T3."Last Name" as c133, T3."No_" as c134, 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 c135, (CASE WHEN (T2."Code" IN ('111')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + 0 + (CASE WHEN (T2."Code" IN ('150')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) as c136, CASE WHEN (T2."Code" IN ('150')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c137, CASE WHEN (T2."Code" IN ('111')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c138, CASE WHEN (T2."Code" IN ('332')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c139, CASE WHEN (T2."Code" IN ('200')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c140, CASE WHEN (T2."Code" IN ('243')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c141, CASE WHEN (T2."Code" IN ('245')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c142, CASE WHEN (T2."Code" IN ('336')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c143, CASE WHEN (T2."Code" IN ('331')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c144, CASE WHEN (T2."Code" IN ('216')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c145, CASE WHEN (T2."Code" IN ('112','113')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c146, CASE WHEN (T2."Code" IN ('242','244')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c147, CASE WHEN (T2."Code" IN ('211','212')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c148, CASE WHEN (T2."Code" IN ('114')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c149, T2."Task Statistic Group" as c150, T2."Description" as c151, T2."Code" as c152, (cast_float(T1."Duration")) as c153, T1."Date" as c154, T1."Employee No_" as c155, T1."Order Line No_" as c156, T1."Labor No_" as c157, T1."Labor Standard Time Type" as c158, (cast_float(T1."Resource Efficiency %")) as c159, T1."Leave" as c160, T1."Location Code" as c161, T1."Tested" as c162, T1."Resource Group No_" as c163, T1."Service Advisor No_" as c164, T1."Posting Time" as c165, T1."Correction Mode" as c166, T1."Ledger Entry Origin" as c167, T1."Applied-to Entry No_" as c168, T1."Subject to Approval" as c169, T1."Automatic Posting" as c170, T1."Approved" as c171, T1."Approved by User ID" as c172, T1."Branch Code" as c173, T1."Time Acquisition Posted" as c174, T1."Duration" as c175, T1."Ending Time" as c176, T1."Starting Time" as c177, T1."Corrected" as c178, T1."Task Ledger Entry No_" as c179, T1."Link No_" as c180, T1."Task Type Code" as c181, T1."Closed" as c182, T1."Service Job No_" as c183, T1."Posting Date" as c184, T1."Sorting" as c185, T1."Reason Code" as c186, T1."User ID" as c187, T1."Make Code" as c188, T1."Department Code" as c189, T1."Terminal Code" as c190, T1."Cause of Absence Code" as c191, T1."Begin_End" as c192, T1."Time Value" as c193, T1."Entry No_" as c194 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."Employee No_" <> '0045')) and (not (T3."First Name" || ' ' || T3."Last Name") 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 c2 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,Vorabdiagnosen COLUMN,48,allg. Werkstattarb. COLUMN,49,Meistervertr. COLUMN,50,Wartezeit COLUMN,51,Fahrtätigkeit COLUMN,52,Schulung intern COLUMN,53,Besprechung COLUMN,54,Erstellung Verkaufsaufträge COLUMN,55,Servicemobil COLUMN,56,Endkontr. für Teamverantw. COLUMN,57,Car Jockey COLUMN,58,Schulung extern COLUMN,59,extern COLUMN,60,GWL COLUMN,61,intern COLUMN,62,produktiv COLUMN,63,Hauptbetrieb COLUMN,64,Standort COLUMN,65,No COLUMN,66,Last Name COLUMN,67,First Name COLUMN,68,Monteur COLUMN,69,W-variabel Stunden COLUMN,70,Datum COLUMN,71,Order Number COLUMN,72,Leaving Date COLUMN,73,Group No 1 COLUMN,74,Group No 2 COLUMN,75,Group No 3 COLUMN,76,Monteur_Gruppe_ori COLUMN,77,Tage Heute Leaving Date COLUMN,78,Monteur_Gruppe COLUMN,79,produktiv/unproduktiv COLUMN,80,Monatserster COLUMN,81,Monatsletzter COLUMN,82,Heute COLUMN,83,Datum Tagesbericht COLUMN,84,W-fix Stunden COLUMN,85,Employment Date COLUMN,86,Department No COLUMN,87,Kunde COLUMN,88,Datum Monteurlisten COLUMN,89,Auftrag_Kunde COLUMN,90,Kunde_oA COLUMN,91,produktiv_für_Berechnung_LG COLUMN,92,Anwesenheit bereinigt_für_erzielte_Anwesenheit COLUMN,93,Summe_Anw_bereinigt_Tag COLUMN,94,erzíelte Anwesenheitsstunden bereinigt COLUMN,95,Anzahl_Datensätze COLUMN,96,erzielte Anwesenheiststunden für Berechnung Soll_Leistung COLUMN,97,Nacharbeit COLUMN,98,krank