COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\Monteur_neu_Zeiterf_neu.imr
TITLE,Monteur_neu_Zeiterf_neu
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" = '2830') THEN (T1."USED_TIME_INT") ELSE (0) END as c38,
	   CASE WHEN (T1."ACTIVITY_CODE" = '2000') THEN (T1."USED_TIME_INT") ELSE (0) END as c39,
	   CASE WHEN (T1."ACTIVITY_CODE" = '2100') THEN (T1."USED_TIME_INT") ELSE (0) END as c40,
	   CASE WHEN (T1."ACTIVITY_CODE" = '2850') THEN (T1."USED_TIME_INT") ELSE (0) END as c41,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1000','1001','1002','1003','1004','1005')) THEN (T1."USED_TIME_INT") ELSE (0) END as c42,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2200','2210','2230','2220','2250','2240')) 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,
	   CASE WHEN (T1."ACTIVITY_CODE" = '4200') THEN (T1."USED_TIME_INT") ELSE (0) END as c46,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5000')) 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,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('4010')) THEN (T1."USED_TIME_INT") ELSE (0) END as c52,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5400')) 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 ('1020','1010','1030','1031','1032','1033','1061','1062')) THEN (T1."USED_TIME_INT") ELSE (0) END as c57,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1050','1053','1051','1052')) THEN (T1."USED_TIME_INT") ELSE (0) END as c58,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1040','1041','1043','1043')) THEN (T1."USED_TIME_INT") ELSE (0) END as c59,
	   (CASE WHEN (T1."ACTIVITY_CODE" IN ('1020','1010','1030','1031','1032','1033','1061','1062')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1050','1053','1051','1052')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1040','1041','1043','1043')) THEN (T1."USED_TIME_INT") ELSE (0) END) as c60,
	   (CASE WHEN (T1."ACTIVITY_CODE" IN ('1000','1001','1002','1003','1004','1005')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '2830') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '2000') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2200','2210','2230','2220','2250','2240')) 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 ('2800')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2820')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2110')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2810')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '2850') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '2100') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '2120') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '2130') 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')) 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 ('1009')) 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" = '4200') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5400')) 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,
	   CASE WHEN ((od_left(T3."WORK_LEADER_GROUP",2)) = '10') THEN (1) WHEN ((od_left(T3."WORK_LEADER_GROUP",2)) = '11') THEN (2) WHEN ((od_left(T3."WORK_LEADER_GROUP",2)) = '12') THEN (3) ELSE null END as c68,
	   (substring(T6."DEPARTMENT_TYPE_ID" from 4 for 1)) as c69,
	   T2."SEL_NAME" as c70,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1009')) THEN (T1."USED_TIME_INT") ELSE (0) END as c71,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5110','5120')) THEN (T1."USED_TIME_INT") ELSE (0) END as c72,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('4100')) THEN (T1."USED_TIME_INT") ELSE (0) END as c73,
	   T7."WORK_LEADER_GROUP_ID" as c74,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2800')) THEN (T1."USED_TIME_INT") ELSE (0) END as c75,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2820')) THEN (T1."USED_TIME_INT") ELSE (0) END as c76,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2110')) THEN (T1."USED_TIME_INT") ELSE (0) END as c77,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2810')) THEN (T1."USED_TIME_INT") ELSE (0) END as c78,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('996')) THEN (T1."USED_TIME_INT") ELSE (0) END as c79,
	   CASE WHEN (T1."PROFILE_CODE" = '34') THEN ('4000 - Azubi') ELSE ((T3."WORK_LEADER_GROUP" || ' - ' || T3."WORKLEADER_TEXT")) END as c80,
	   CASE WHEN (T1."ACTIVITY_CODE" = '2120') THEN (T1."USED_TIME_INT") ELSE (0) END as c81,
	   CASE WHEN (T1."ACTIVITY_CODE" = '2130') THEN (T1."USED_TIME_INT") ELSE (0) END as c82
from "deop02"."dbo"."EMPLOYEE" T7,
	"deop02"."dbo"."vPP91" T3,
	((((("deop02"."dbo"."PUNCH" T1 left outer join "deop02"."dbo"."vPP43" T2 on T1."PROFILE_CODE" = T2."SELLER_CODE") left outer join "deop02"."dbo"."PROFILE" T8 on T2."SELLER_CODE" = T8."PROFILE_CODE") left outer join "deop02"."dbo"."ACTIVITY" T4 on T1."ACTIVITY_CODE" = T4."ACTIVITY_CODE") left outer join "deop02"."dbo"."ORDER_HEADER" T5 on T5."ORDER_NUMBER" = T1."ORDER_NUMBER") left outer join "deop02"."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 ((T1."TRANSACT_DATE_LONG" >= TIMESTAMP '2013-01-01 00:00:00.000') and ((CASE WHEN (T1."PROFILE_CODE" = '34') THEN ('4000 - Azubi') ELSE ((T3."WORK_LEADER_GROUP" || ' - ' || T3."WORKLEADER_TEXT")) END) IN ('1010 - ÖSI Mechanik','1020 - ÖSI Karosserie','1030 - ÖSI Lack','1040 - ÖSI Aufbereiter','1110 - PIR Mechanik','1140 - PIR Aufbereiter')))
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,Leerlauf/Wartezeit
COLUMN,39,Werkstattpflege
COLUMN,40,GW-Bewertung
COLUMN,41,Unprod. Anwes.
COLUMN,42,Hilfslohn
COLUMN,43,Schulung intern
COLUMN,44,Schulung extern
COLUMN,45,Zeitausgleich (alt)
COLUMN,46,Krank
COLUMN,47,Arzt
COLUMN,48,Urlaub
COLUMN,49,Sonderurlaub
COLUMN,50,Feiertag
COLUMN,51,zu spät
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_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,Tag beenden
COLUMN,71,Berufsschule
COLUMN,72,unbez. Abwes.
COLUMN,73,Work Leader Group Id
COLUMN,74,Aufbereitung
COLUMN,75,Waschanlage
COLUMN,76,Mach mal schnell
COLUMN,77,Transport
COLUMN,78,Abzug T390
COLUMN,79,Monteur_Gruppe
COLUMN,80,Annahme KD
COLUMN,81,Lack ohne Auftrag