GAPS/Portal/System/IQD/Zeiten/Monteur_neu_Zeiterf_neu_final.iqd

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