COGNOS QUERY STRUCTURE,1,1 DATABASE,O21 DATASOURCENAME,D:\Gaps\Portal\System\IQD\Zeiten\Monteur_neu_Zeiterf_neu.imr TITLE,Monteur_neu_Zeiterf_neu.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, 0 as c38, CASE WHEN (T1."ACTIVITY_CODE" IN ('2000')) THEN (T1."USED_TIME_INT") ELSE (0) END as c39, '' as c40, 0 as c41, CASE WHEN (T1."ACTIVITY_CODE" IN ('2110','2130','2120')) THEN (T1."USED_TIME_INT") ELSE (0) END as c42, CASE WHEN (T1."ACTIVITY_CODE" IN ('2200','2210','2220')) THEN (T1."USED_TIME_INT") ELSE (0) END as c43, CASE WHEN (T1."ACTIVITY_CODE" IN ('2900')) THEN (T1."USED_TIME_INT") ELSE (0) END as c44, CASE WHEN (T1."ACTIVITY_CODE" IN ('5100')) THEN (T1."USED_TIME_INT") ELSE (0) END as c45, '' as c46, CASE WHEN (T1."ACTIVITY_CODE" IN ('5000','5010','3100')) THEN (T1."USED_TIME_INT") ELSE (0) END as c47, CASE WHEN (T1."ACTIVITY_CODE" IN ('3000')) THEN (T1."USED_TIME_INT") ELSE (0) END as c48, CASE WHEN (T1."ACTIVITY_CODE" IN ('5200')) THEN (T1."USED_TIME_INT") ELSE (0) END as c49, CASE WHEN (T1."ACTIVITY_CODE" IN ('5210')) THEN (T1."USED_TIME_INT") ELSE (0) END as c50, CASE WHEN (T1."ACTIVITY_CODE" IN ('5300')) THEN (T1."USED_TIME_INT") ELSE (0) END as c51, '' as c52, CASE WHEN (T1."ACTIVITY_CODE" IN ('4200')) 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 ('1010','1011','1L20','1012','1L10','1K12','1L22','1K11','1K10','1L11','1K20','1L31','1L21','1L30','1K21','1K22','1L12','1L32','1013')) THEN (T1."USED_TIME_INT") ELSE (0) END as c57, CASE WHEN (T1."ACTIVITY_CODE" IN ('1031','1032','1033')) THEN (T1."USED_TIME_INT") ELSE (0) END as c58, CASE WHEN (T1."ACTIVITY_CODE" IN ('1020','1021','1022','1023','1K80','1K81','1K82','1K90','1K91','1K92','1L70','1L71','1L72','1L80','1L81','1L82','1L90','1L91','1L92')) THEN (T1."USED_TIME_INT") ELSE (0) END as c59, (CASE WHEN (T1."ACTIVITY_CODE" IN ('1010','1011','1L20','1012','1L10','1K12','1L22','1K11','1K10','1L11','1K20','1L31','1L21','1L30','1K21','1K22','1L12','1L32','1013')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1031','1032','1033')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1020','1021','1022','1023','1K80','1K81','1K82','1K90','1K91','1K92','1L70','1L71','1L72','1L80','1L81','1L82','1L90','1L91','1L92')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2100')) THEN (T1."USED_TIME_INT") ELSE (0) END) as c60, (CASE WHEN (T1."ACTIVITY_CODE" IN ('2110','2130','2120')) 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 ('2200','2210','2220')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2900')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1001','1000','1002','1003')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5130')) THEN (T1."USED_TIME_INT") ELSE (0) END) as c61, (CASE WHEN (T1."ACTIVITY_CODE" IN ('5100')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5000','5010','3100')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('3000')) 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 ('5210')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5110','5120')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5300')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('996')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5220')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('4200')) 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 ('4100')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1009')) 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, (rtrim(T1."PROFILE_CODE")) || ' - ' || (T2."SEL_NAME") as c70, '' as c71, CASE WHEN (T1."ACTIVITY_CODE" IN ('5110','5120')) THEN (T1."USED_TIME_INT") ELSE (0) END as c72, '' as c73, T7."WORK_LEADER_GROUP_ID" as c74, '' as c75, '' as c76, CASE WHEN (T1."ACTIVITY_CODE" IN ('1009')) THEN (T1."USED_TIME_INT") ELSE (0) END as c77, '' as c78, CASE WHEN (T1."ACTIVITY_CODE" IN ('996')) THEN (T1."USED_TIME_INT") ELSE (0) END as c79, CASE WHEN (T1."ACTIVITY_CODE" IN ('1001','1000','1002','1003')) THEN (T1."USED_TIME_INT") ELSE (0) END as c80, CASE WHEN (T1."ACTIVITY_CODE" IN ('2100')) THEN (T1."USED_TIME_INT") ELSE (0) END as c81, CASE WHEN (T1."ACTIVITY_CODE" IN ('5130')) THEN (T1."USED_TIME_INT") ELSE (0) END as c82, CASE WHEN (T1."ACTIVITY_CODE" IN ('5220')) THEN (T1."USED_TIME_INT") ELSE (0) END as c83, CASE WHEN (T1."ACTIVITY_CODE" IN ('4100')) THEN (T1."USED_TIME_INT") ELSE (0) END as c84, CASE WHEN (T1."ACTIVITY_CODE" IN ('4010')) THEN (T1."USED_TIME_INT") ELSE (0) END as c85, CASE WHEN (T1."PROFILE_CODE" = '34') THEN ('4000 - Azubi') ELSE ((T3."WORK_LEADER_GROUP" || ' - ' || T3."WORKLEADER_TEXT")) END as c86 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 '2011-01-01') and (not T1."ACTIVITY_CODE" IN ('996'))) order by c7 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,Zeitausgleich 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_ori 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,Werkstatt aufräumen_ COLUMN,71,Berufsschule COLUMN,72,Kurzarbeit_ COLUMN,73,Work Leader Group Id COLUMN,74,Leerlauf_ COLUMN,75,Aufräumarbeiten_ COLUMN,76,Tag beenden COLUMN,77,Rg Stempeln_ COLUMN,78,Abzug T390 COLUMN,79,unprod. Anwesenh. COLUMN,80,Abschleppen COLUMN,81,Ausstellung COLUMN,82,Erziehungsurlaub COLUMN,83,unbez Abwesenh. COLUMN,84,spät. AZ Beginn COLUMN,85,Monteur_Gruppe