COGNOS QUERY STRUCTURE,1,1 DATABASE,O21 DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\Monteur_neu_Zeiterf_neu_final.imr TITLE,Monteur_neu_Zeiterf_neu_final.imr BEGIN SQL select T1."UNIQUE_IDENT" as c1, T1."ACTIVITY_CODE" as c2, T1."PROFILE_CODE" as c3, T1."HANDLER" as c4, T1."FUNCTION_CODE" as c5, T1."PROGRAM" as c6, T1."TRANSACT_DATE_LONG" as c7, T1."START_PUNCH_FUNCTION" as c8, T1."START_PUNCH_PROGRAM" as c9, T1."END_PUNCH_FUNCTION" as c10, T1."END_PUNCH_PROGRAM" as c11, T1."DONE_FOR_DEPARTMENT" as c12, T1."DONE_FOR_WORK_LEADER" as c13, T1."ENDED_PUNCH" as c14, T1."ORDER_NUMBER" as c15, T1."LINE_NUMBER" as c16, T1."START_DATE_TIME" as c17, T1."END_DATE_TIME" as c18, T1."TMCS_IDLE_PUNCH" as c19, T1."DURATION_INT" as c20, T1."USED_TIME_INT" as c21, T1."WAGE_EXTRACTED" as c22, T1."PUNCH_REMARK_CODE" as c23, T1."REMARK_ACCEPTED" as c24, T1."PUNCH_PERIOD_START_ID" as c25, T1."CONV_FLAG" as c26, T2."SELLER_CODE" as c27, T2."SEL_NAME" as c28, T2."SEL_DEPARTMENT" as c29, T2."SEL_FIRST_NAME" as c30, T2."SEL_FAMILY_NAME" as c31, T3."WORK_LEADER_GROUP" as c32, T3."WORKLEADER_TEXT" as c33, T4."ACTIVITY_CODE" as c34, T4."ACTIVITY_DESCRIPTION" as c35, T4."PRESENT" as c36, CASE WHEN (T4."PRESENT" = 1) THEN (T1."USED_TIME_INT") ELSE (0) END as c37, CASE WHEN (T1."ACTIVITY_CODE" = '1011') THEN (T1."USED_TIME_INT") ELSE (0) END as c38, '' as c39, 0 as c40, 0 as c41, CASE WHEN (T1."ACTIVITY_CODE" IN ('2210','2220')) THEN (T1."USED_TIME_INT") ELSE (0) END as c42, CASE WHEN (T1."ACTIVITY_CODE" IN ('2310','2360','2370')) THEN (T1."USED_TIME_INT") ELSE (0) END as c43, CASE WHEN (T1."ACTIVITY_CODE" IN ('2910')) THEN (T1."USED_TIME_INT") ELSE (0) END as c44, CASE WHEN (T1."ACTIVITY_CODE" IN ('5300')) THEN (T1."USED_TIME_INT") ELSE (0) END as c45, CASE WHEN (T1."ACTIVITY_CODE" IN ('2800')) THEN (T1."USED_TIME_INT") ELSE (0) END as c46, CASE WHEN (T1."ACTIVITY_CODE" IN ('5100')) THEN (T1."USED_TIME_INT") ELSE (0) END as c47, CASE WHEN (T1."ACTIVITY_CODE" IN ('5110')) THEN (T1."USED_TIME_INT") ELSE (0) END as c48, CASE WHEN (T1."ACTIVITY_CODE" IN ('5010')) THEN (T1."USED_TIME_INT") ELSE (0) END as c49, CASE WHEN (T1."ACTIVITY_CODE" IN ('5020')) THEN (T1."USED_TIME_INT") ELSE (0) END as c50, CASE WHEN (T1."ACTIVITY_CODE" IN ('5200')) THEN (T1."USED_TIME_INT") ELSE (0) END as c51, CASE WHEN (T1."ACTIVITY_CODE" IN ('4010')) THEN (T1."USED_TIME_INT") ELSE (0) END as c52, CASE WHEN (T1."ACTIVITY_CODE" IN ('4020')) THEN (T1."USED_TIME_INT") ELSE (0) END as c53, T5."ORDER_NUMBER" as c54, T5."CUSTOMER_GROUP" as c55, CASE WHEN ((T5."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T5."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T5."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN (((T5."CUSTOMER_GROUP" LIKE '9%') or (T5."PMT_TERM" = 'IN')) or ((od_left(T5."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END as c56, CASE WHEN (T1."ACTIVITY_CODE" IN ('2100','2110','1010','1020')) THEN (T1."USED_TIME_INT") ELSE (0) END as c57, CASE WHEN (T1."ACTIVITY_CODE" IN ('1050')) THEN (T1."USED_TIME_INT") ELSE (0) END as c58, CASE WHEN (T1."ACTIVITY_CODE" IN ('1040','1030')) THEN (T1."USED_TIME_INT") ELSE (0) END as c59, (CASE WHEN (T1."ACTIVITY_CODE" IN ('2100','2110','1010','1020')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1050')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1040','1030')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2310','2360','2370')) THEN (T1."USED_TIME_INT") ELSE (0) END) as c60, (CASE WHEN (T1."ACTIVITY_CODE" IN ('2210','2220')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '1011') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2000')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2910')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2800')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1000','1001','1002','1003')) THEN (T1."USED_TIME_INT") ELSE (0) END) as c61, (CASE WHEN (T1."ACTIVITY_CODE" IN ('5300')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5100')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5110')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5010')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5020')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('4010')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5310')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5200')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('996')) THEN (T1."USED_TIME_INT") ELSE (0) END) as c62, T3."WORK_LEADER_GROUP" || ' - ' || T3."WORKLEADER_TEXT" as c63, (cdate(T1."START_DATE_TIME")) as c64, '1' as c65, T6."DEPARTMENT_TYPE_ID" as c66, T6."DESCRIPTION" as c67, (od_left(T6."DEPARTMENT_TYPE_ID",2)) as c68, (substring(T6."DEPARTMENT_TYPE_ID" from 4 for 1)) as c69, (T1."PROFILE_CODE" || ' - ' || T2."SEL_NAME") as c70, CASE WHEN (T1."ACTIVITY_CODE" IN ('5310')) THEN (T1."USED_TIME_INT") ELSE (0) END as c71, CASE WHEN (T1."ACTIVITY_CODE" IN ('5500')) THEN (T1."USED_TIME_INT") ELSE (0) END as c72, CASE WHEN (T1."ACTIVITY_CODE" IN ('2000')) THEN (T1."USED_TIME_INT") ELSE (0) END as c73, '' as c74, '' as c75, '' as c76, CASE WHEN (T1."ACTIVITY_CODE" IN ('996')) THEN (T1."USED_TIME_INT") ELSE (0) END as c77, CASE WHEN (T1."ACTIVITY_CODE" IN ('1000','1001','1002','1003')) THEN (T1."USED_TIME_INT") ELSE (0) END as c78, CASE WHEN (T1."ACTIVITY_CODE" IN ('1010','1020','1030','1040','1050')) THEN (T1."USED_TIME_INT") ELSE (0) END as c79, CASE WHEN (T1."ACTIVITY_CODE" IN ('2100','2110')) THEN (T1."USED_TIME_INT") ELSE (0) END as c80, T7."WORK_LEADER_GROUP_ID" as c81, T7."EMPLOYEE_GROUP" as c82, CASE WHEN (T1."ACTIVITY_CODE" IN ('2100')) THEN ('deci01') WHEN (T1."ACTIVITY_CODE" IN ('2110')) THEN ('defi01') ELSE ('deop01') END as c83 from "deop01"."dbo"."EMPLOYEE" T7, "deop01"."dbo"."vPP91" T3, ((((("deop01"."dbo"."PUNCH" T1 left outer join "deop01"."dbo"."vPP43" T2 on T1."PROFILE_CODE" = T2."SELLER_CODE") left outer join "deop01"."dbo"."PROFILE" T8 on T2."SELLER_CODE" = T8."PROFILE_CODE") left outer join "deop01"."dbo"."ACTIVITY" T4 on T1."ACTIVITY_CODE" = T4."ACTIVITY_CODE") left outer join "deop01"."dbo"."ORDER_HEADER" T5 on T5."ORDER_NUMBER" = T1."ORDER_NUMBER") left outer join "deop01"."dbo"."DEPARTMENT_TYPE" T6 on T2."SEL_DEPARTMENT" = T6."DEPARTMENT_TYPE_ID") where (T7."PERSON_ID" = T8."PERSON_ID") and (T3."WORK_LEADER_GROUP" = T7."WORK_LEADER_GROUP_ID") and ((((((cdate(T1."START_DATE_TIME"))) >= DATE '2012-01-01') and (not T3."WORK_LEADER_GROUP" LIKE '4%')) and (T3."WORK_LEADER_GROUP" <> '6100')) and (not T2."SEL_NAME" IN ('Dietmar Märtens ','Dirk Döring ','Lorenz Kaun ','Lothar Warnke ','Manuel Schröder ','Robert Ehrlich ','Maik Jahnel ','Marc Ebert ','Patrick Teutschbein ','Karsten Liepe ','Stephan Malchow ','Tony Weber ','Mario Schröder ','Christian Aul','Vanessa Knaak'))) order by c64 asc END SQL COLUMN,0,Unique Ident COLUMN,1,Activity Code COLUMN,2,Profile Code COLUMN,3,Handler COLUMN,4,Function Code COLUMN,5,Program COLUMN,6,Transact Date Long COLUMN,7,Start Punch Function COLUMN,8,Start Punch Program COLUMN,9,End Punch Function COLUMN,10,End Punch Program COLUMN,11,Done For Department COLUMN,12,Done For Work Leader COLUMN,13,Ended Punch COLUMN,14,Order Number COLUMN,15,Line Number COLUMN,16,Start Date Time COLUMN,17,End Date Time COLUMN,18,Tmcs Idle Punch COLUMN,19,Duration Int COLUMN,20,Used Time Int COLUMN,21,Wage Extracted COLUMN,22,Punch Remark Code COLUMN,23,Remark Accepted COLUMN,24,Punch Period Start Id COLUMN,25,Conv Flag COLUMN,26,Seller Code COLUMN,27,Sel Name COLUMN,28,Sel Department COLUMN,29,Sel First Name COLUMN,30,Sel Family Name COLUMN,31,Work Leader Group COLUMN,32,Workleader Text COLUMN,33,Activity Code_Activity COLUMN,34,Activity Description COLUMN,35,Present_Activity COLUMN,36,Anwesenheit COLUMN,37,Nacharbeit_ COLUMN,38,Wartezeit_ COLUMN,39,Prob.fahrt/Endkontr._ COLUMN,40,GW-Bewertung_ COLUMN,41,Unproduktiv COLUMN,42,Hilfslohn COLUMN,43,Schulung intern COLUMN,44,Schulung extern COLUMN,45,Betriebsrat_ COLUMN,46,Krank COLUMN,47,Arzt COLUMN,48,Urlaub COLUMN,49,Sonderurlaub COLUMN,50,Feiertag COLUMN,51,Fehlstunden COLUMN,52,Überstunden COLUMN,53,Order Number_Auftrag COLUMN,54,Customer Group COLUMN,55,Umsatzart COLUMN,56,Extern COLUMN,57,GWL COLUMN,58,Intern COLUMN,59,produktiv COLUMN,60,unproduktiv COLUMN,61,abwesend COLUMN,62,Monteur_Gruppe COLUMN,63,Datum COLUMN,64,Hauptbetrieb COLUMN,65,Department Type Id COLUMN,66,Description COLUMN,67,Standort COLUMN,68,Kostenstelle COLUMN,69,Monteur COLUMN,70,Berufsschule COLUMN,71,Kurzarbeit_ COLUMN,72,Leerlauf COLUMN,73,Aufräumarbeiten_ COLUMN,74,Reparaturannahme_ COLUMN,75,Rg Stempeln_ COLUMN,76,Abzug T390 COLUMN,77,unprod. Anwesenh. COLUMN,78,prod. Opel COLUMN,79,prod. CI & FI COLUMN,80,Work Leader Group Id_Empl COLUMN,81,Employee Group COLUMN,82,Mandant