C11/System/OPTIMA/IQD/Zeiten/Monteur_neu_Zeiterf_neu.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GlobalCube\System\OPTIMA\IQD\zeiten\Monteur_neu_Zeiterf_neu.imr
TITLE,Monteur_neu_Zeiterf_neu.imr
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,
	   RSUM(c37) as c38,
	   c39 as c39,
	   c40 as c40,
	   c41 as c41,
	   c42 as c42,
	   c43 as c43,
	   c44 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,
	   RSUM(c61) as c62,
	   c63 as c63,
	   RSUM(c63) 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
from 
(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" IN ('2830')) THEN (T1."USED_TIME_INT") ELSE (0) END as c39,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2000')) THEN (T1."USED_TIME_INT") ELSE (0) END as c40,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('HUB ')) THEN (T1."USED_TIME_INT") ELSE (0) END as c41,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('W1  ','2210')) THEN (T1."USED_TIME_INT") ELSE (0) END as c42,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2100','1001','1000','1005','1004','2110')) THEN (T1."USED_TIME_INT") ELSE (0) END as c43,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2200','2210','2220','2250','2230')) THEN (T1."USED_TIME_INT") ELSE (0) END as c44,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2900')) THEN (T1."USED_TIME_INT") ELSE (0) END as c45,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5100')) THEN (T1."USED_TIME_INT") ELSE (0) END as c46,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2800')) THEN (T1."USED_TIME_INT") ELSE (0) END as c47,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5000  ')) THEN (T1."USED_TIME_INT") ELSE (0) END as c48,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('3000')) THEN (T1."USED_TIME_INT") ELSE (0) END as c49,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5200')) THEN (T1."USED_TIME_INT") ELSE (0) END as c50,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5210')) THEN (T1."USED_TIME_INT") ELSE (0) END as c51,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5300')) THEN (T1."USED_TIME_INT") ELSE (0) END as c52,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('4010')) THEN (T1."USED_TIME_INT") ELSE (0) END as c53,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5400')) THEN (T1."USED_TIME_INT") ELSE (0) END as c54,
	   T5."ORDER_NUMBER" as c55,
	   T5."CUSTOMER_GROUP" as c56,
	   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 c57,
	   CASE WHEN (T6."Zeitkategorie_2" IN ('Extern')) THEN (T1."USED_TIME_INT") ELSE (0) END as c58,
	   CASE WHEN (T6."Zeitkategorie_2" IN ('GWL')) THEN (T1."USED_TIME_INT") ELSE (0) END as c59,
	   CASE WHEN (T6."Zeitkategorie_2" IN ('Intern')) THEN (T1."USED_TIME_INT") ELSE (0) END as c60,
	   (CASE WHEN (T6."Zeitkategorie_2" IN ('Extern')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T6."Zeitkategorie_2" IN ('GWL')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T6."Zeitkategorie_2" IN ('Intern')) THEN (T1."USED_TIME_INT") ELSE (0) END) as c61,
	   CASE WHEN (T6."Zeitkategorie_1" IN ('unproduktiv')) THEN (T1."USED_TIME_INT") ELSE (0) END as c63,
	   CASE WHEN (T6."Zeitkategorie_1" IN ('Abwesenheit')) THEN (T1."USED_TIME_INT") ELSE (0) END as c65,
	   T3."WORK_LEADER_GROUP" || ' - ' || T3."WORKLEADER_TEXT" as c66,
	   (cdate(T1."START_DATE_TIME")) as c67,
	   T7."CLIENT_DB" as c68,
	   T8."DEPARTMENT_TYPE_ID" as c69,
	   T8."DESCRIPTION" as c70,
	   (od_left(T2."SEL_DEPARTMENT",2)) as c71,
	   (substring(T2."SEL_DEPARTMENT" from 4 for 1)) as c72,
	   T2."SEL_NAME" as c73,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2999')) THEN (T1."USED_TIME_INT") ELSE (0) END as c74,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('3920')) THEN (T1."USED_TIME_INT") ELSE (0) END as c75,
	   '' as c76,
	   T7."WORK_LEADER_GROUP_ID" as c77,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2850')) THEN (T1."USED_TIME_INT") ELSE (0) END as c78,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2810')) THEN (T1."USED_TIME_INT") ELSE (0) END as c79,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2820')) THEN (T1."USED_TIME_INT") ELSE (0) END as c80,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2840')) THEN (T1."USED_TIME_INT") ELSE (0) END as c81,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('996')) THEN (T1."USED_TIME_INT") ELSE (0) END as c82,
	   (T3."WORK_LEADER_GROUP" || ' - ' || T3."WORKLEADER_TEXT") as c83,
	   T1."PROFILE_CODE" || ' - ' || T2."SEL_NAME" as c84,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1009')) THEN (T1."USED_TIME_INT") ELSE (0) END as c85,
	   T6."Zeitkategorie_1" as c86,
	   T6."Zeitkategorie_2" as c87,
	   T6."Activity_Code" || ' - ' || T6."Activity_Desc" as c88,
	   T9."Hauptbetrieb_ID" as c89,
	   T9."Hauptbetrieb_Name" as c90,
	   T9."Standort_ID" as c91,
	   T9."Standort_Name" as c92
from "OPTIMA"."import"."EMPLOYEE" T7,
	"OPTIMA"."import"."VPP91" T3,
	((((((("OPTIMA"."import"."PUNCH" T1 left outer join "OPTIMA"."import"."VPP43" T2 on (T1."PROFILE_CODE" = T2."SELLER_CODE") and (T1."CLIENT_DB" = T2."CLIENT_DB")) left outer join "OPTIMA"."import"."PROFILE" T10 on (T2."SELLER_CODE" = T10."PROFILE_CODE") and (T2."CLIENT_DB" = T10."CLIENT_DB")) left outer join "OPTIMA"."import"."ACTIVITY" T4 on (T1."ACTIVITY_CODE" = T4."ACTIVITY_CODE") and (T1."CLIENT_DB" = T4."CLIENT_DB")) left outer join "OPTIMA"."import"."ORDER_HEADER" T5 on (T5."ORDER_NUMBER" = T1."ORDER_NUMBER") and (T5."CLIENT_DB" = T1."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Activity_Codes" T6 on (T4."CLIENT_DB" = T6."Client_DB") and (T4."ACTIVITY_CODE" = T6."Activity_Code")) left outer join "OPTIMA"."import"."DEPARTMENT_TYPE" T8 on (T2."SEL_DEPARTMENT" = T8."DEPARTMENT_TYPE_ID") and (T2."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Department" T9 on (T2."CLIENT_DB" = T9."Hauptbetrieb") and ((od_left(T2."SEL_DEPARTMENT",2)) = T9."Standort"))
where ((T7."PERSON_ID" = T10."PERSON_ID") and (T7."CLIENT_DB" = T10."CLIENT_DB")) and ((T3."WORK_LEADER_GROUP" = T7."WORK_LEADER_GROUP_ID") and (T3."CLIENT_DB" = T7."CLIENT_DB"))
and (((not T1."PROFILE_CODE" IN ('MARE')) and (((cdate(T1."START_DATE_TIME"))) >= DATE '2020-01-01')) and (((T3."WORK_LEADER_GROUP" || ' - ' || T3."WORKLEADER_TEXT")) IN ('110  - WIZ Mechanik                  ','120  - WIZ Karosserie                ','140  - WIZ Lehrlinge                 ','310  - ESW Mechanik                  ','320  - ESW Karosserie                ','340  - ESW Lehrlinge                 ','510  - LPH Mechanik','520  - LPH Karosserie','540  - LPH Lehrlinge')))
order by c7 asc
) D1

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,Summe  (Anwesenheit) Nr.1
COLUMN,38,Nacharbeit
COLUMN,39,Leerlauf/Wartezeit
COLUMN,40,Hol u Bring Service
COLUMN,41,Instandhltg. Werkstatt
COLUMN,42,Unprod. Anwes.
COLUMN,43,Hilfslohn
COLUMN,44,Schulung intern
COLUMN,45,Schulung extern
COLUMN,46,Reifenlager
COLUMN,47,Krank
COLUMN,48,Arzt
COLUMN,49,Urlaub
COLUMN,50,Sonderurlaub
COLUMN,51,Feiertag
COLUMN,52,zu spät
COLUMN,53,Überstunden
COLUMN,54,Order Number_Auftrag
COLUMN,55,Customer Group
COLUMN,56,Umsatzart
COLUMN,57,Extern
COLUMN,58,GWL
COLUMN,59,Intern
COLUMN,60,produktiv
COLUMN,61,Summe  (produktiv) Nr.1
COLUMN,62,unproduktiv
COLUMN,63,Summe  (unproduktiv) Nr.1
COLUMN,64,abwesend
COLUMN,65,Monteur_Gruppe_ori
COLUMN,66,Datum
COLUMN,67,Hauptbetrieb
COLUMN,68,Department Type Id
COLUMN,69,Description
COLUMN,70,Standort
COLUMN,71,Kostenstelle
COLUMN,72,Monteur_ori
COLUMN,73,Konv Zeitkonto minus
COLUMN,74,Berufsschule
COLUMN,75,Ausb.Zeit mit Handwerker nur Azubi_
COLUMN,76,Work Leader Group Id
COLUMN,77,GW-Bewertung
COLUMN,78,Waschanlage
COLUMN,79,Sondereinsatz lt. WL
COLUMN,80,Abschleppen
COLUMN,81,Abzug T390
COLUMN,82,Monteur_Gruppe
COLUMN,83,Monteur
COLUMN,84,Tag beenden
COLUMN,85,Activity_Codes_Group1
COLUMN,86,Activity_Codes_Group2
COLUMN,87,Activity_Desc
COLUMN,88,Hauptbetrieb Id
COLUMN,89,Hauptbetrieb Name
COLUMN,90,Standort Id
COLUMN,91,Standort Name