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

select "Unique Ident" as "Unique Ident",
	   "Activity Code" as "Activity Code",
	   "Profile Code" as "Profile Code",
	   "Handler" as "Handler",
	   "Function Code" as "Function Code",
	   "Program" as "Program",
	   "Transact Date Long" as "Transact Date Long",
	   "Start Punch Function" as "Start Punch Function",
	   "Start Punch Program" as "Start Punch Program",
	   "End Punch Function" as "End Punch Function",
	   "End Punch Program" as "End Punch Program",
	   "Done For Department" as "Done For Department",
	   "Done For Work Leader" as "Done For Work Leader",
	   "Ended Punch" as "Ended Punch",
	   "Order Number" as "Order Number",
	   "Line Number" as "Line Number",
	   "Start Date Time" as "Start Date Time",
	   "End Date Time" as "End Date Time",
	   "Tmcs Idle Punch" as "Tmcs Idle Punch",
	   "Duration Int" as "Duration Int",
	   "Used Time Int" as "Used Time Int",
	   "Wage Extracted" as "Wage Extracted",
	   "Punch Remark Code" as "Punch Remark Code",
	   "Remark Accepted" as "Remark Accepted",
	   "Punch Period Start Id" as "Punch Period Start Id",
	   "Conv Flag" as "Conv Flag",
	   "Seller Code" as "Seller Code",
	   "Sel Name" as "Sel Name",
	   "Sel Department" as "Sel Department",
	   "Sel First Name" as "Sel First Name",
	   "Sel Family Name" as "Sel Family Name",
	   "Work Leader Group" as "Work Leader Group",
	   "Workleader Text" as "Workleader Text",
	   "Activity Code_Activity" as "Activity Code_Activity",
	   "Activity Description" as "Activity Description",
	   "Present_Activity" as "Present_Activity",
	   "Anwesenheit" as "Anwesenheit",
	   "Nacharbeit" as "Nacharbeit",
	   "Leerlauf/Wartezeit" as "Leerlauf/Wartezeit",
	   "Prob.fahrt/Endkontr._" as "Prob.fahrt/Endkontr._",
	   "Instandhltg. Werkstatt" as "Instandhltg. Werkstatt",
	   "Unprod. Anwes." as "Unprod. Anwes.",
	   "Hilfslohn" as "Hilfslohn",
	   "Schulung intern" as "Schulung intern",
	   "Schulung extern" as "Schulung extern",
	   "Reifenwechsel" as "Reifenwechsel",
	   "Krank" as "Krank",
	   "Arzt" as "Arzt",
	   "Urlaub" as "Urlaub",
	   "Sonderurlaub" as "Sonderurlaub",
	   "Feiertag" as "Feiertag",
	   "zu spät" as "zu spät",
	   "Überstunden" as "Überstunden",
	   "Order Number_Auftrag" as "Order Number_Auftrag",
	   "Customer Group" as "Customer Group",
	   "Umsatzart" as "Umsatzart",
	   "Extern" as "Extern",
	   "GWL" as "GWL",
	   "Intern" as "Intern",
	   "produktiv" as "produktiv",
	   RSUM("produktiv") as "Summe  (produktiv) Nr.1",
	   "unproduktiv" as "unproduktiv",
	   RSUM("unproduktiv") as "Summe  (unproduktiv) Nr.1",
	   "abwesend" as "abwesend",
	   "Monteur_Gruppe_ori" as "Monteur_Gruppe_ori",
	   "Datum" as "Datum",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Department Type Id" as "Department Type Id",
	   "Description" as "Description",
	   "Standort" as "Standort",
	   "Kostenstelle" as "Kostenstelle",
	   "Monteur_ori" as "Monteur_ori",
	   "Konv Zeitkonto minus" as "Konv Zeitkonto minus",
	   "Berufsschule" as "Berufsschule",
	   "Ausb.Zeit mit Handwerker nur Azubi" as "Ausb.Zeit mit Handwerker nur Azubi",
	   "Work Leader Group Id" as "Work Leader Group Id",
	   "Fahrten für KDD_" as "Fahrten für KDD_",
	   "MW tanken_" as "MW tanken_",
	   "Arbeiten Anlage B_" as "Arbeiten Anlage B_",
	   "Aushilfe Annahme_" as "Aushilfe Annahme_",
	   "Abzug T390" as "Abzug T390",
	   "Monteur_Gruppe" as "Monteur_Gruppe",
	   "Monteur" as "Monteur",
	   "Aushilfe GW_" as "Aushilfe GW_",
	   "Activity_Codes_Group1" as "Activity_Codes_Group1",
	   "Activity_Codes_Group2" as "Activity_Codes_Group2",
	   "Activity_Desc" as "Activity_Desc",
	   "Hauptbetrieb Id" as "Hauptbetrieb Id",
	   "Hauptbetrieb Name" as "Hauptbetrieb Name",
	   "Standort Id" as "Standort Id",
	   "Standort Name" as "Standort Name"
from 
(select T1."UNIQUE_IDENT" as "Unique Ident",
	   T1."ACTIVITY_CODE" as "Activity Code",
	   T1."PROFILE_CODE" as "Profile Code",
	   T1."HANDLER" as "Handler",
	   T1."FUNCTION_CODE" as "Function Code",
	   T1."PROGRAM" as "Program",
	   T1."TRANSACT_DATE_LONG" as "Transact Date Long",
	   T1."START_PUNCH_FUNCTION" as "Start Punch Function",
	   T1."START_PUNCH_PROGRAM" as "Start Punch Program",
	   T1."END_PUNCH_FUNCTION" as "End Punch Function",
	   T1."END_PUNCH_PROGRAM" as "End Punch Program",
	   T1."DONE_FOR_DEPARTMENT" as "Done For Department",
	   T1."DONE_FOR_WORK_LEADER" as "Done For Work Leader",
	   T1."ENDED_PUNCH" as "Ended Punch",
	   T1."ORDER_NUMBER" as "Order Number",
	   T1."LINE_NUMBER" as "Line Number",
	   T1."START_DATE_TIME" as "Start Date Time",
	   T1."END_DATE_TIME" as "End Date Time",
	   T1."TMCS_IDLE_PUNCH" as "Tmcs Idle Punch",
	   T1."DURATION_INT" as "Duration Int",
	   T1."USED_TIME_INT" as "Used Time Int",
	   T1."WAGE_EXTRACTED" as "Wage Extracted",
	   T1."PUNCH_REMARK_CODE" as "Punch Remark Code",
	   T1."REMARK_ACCEPTED" as "Remark Accepted",
	   T1."PUNCH_PERIOD_START_ID" as "Punch Period Start Id",
	   T1."CONV_FLAG" as "Conv Flag",
	   T2."SELLER_CODE" as "Seller Code",
	   T2."SEL_NAME" as "Sel Name",
	   T2."SEL_DEPARTMENT" as "Sel Department",
	   T2."SEL_FIRST_NAME" as "Sel First Name",
	   T2."SEL_FAMILY_NAME" as "Sel Family Name",
	   T3."WORK_LEADER_GROUP" as "Work Leader Group",
	   T3."WORKLEADER_TEXT" as "Workleader Text",
	   T4."ACTIVITY_CODE" as "Activity Code_Activity",
	   T4."ACTIVITY_DESCRIPTION" as "Activity Description",
	   T4."PRESENT" as "Present_Activity",
	   CASE WHEN (T4."PRESENT" = 1) THEN (T1."USED_TIME_INT") ELSE (0) END as "Anwesenheit",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('W4  ','2200')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Nacharbeit",
	   CASE WHEN ((substring(T1."ACTIVITY_CODE", 2, 1)) = 'L') THEN (T1."USED_TIME_INT") ELSE (0) END as "Leerlauf/Wartezeit",
	   '' as "Prob.fahrt/Endkontr._",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('W1  ','2210')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Instandhltg. Werkstatt",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1000','1002','1001','1003  ')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Unprod. Anwes.",
	   CASE WHEN ((substring(T1."ACTIVITY_CODE", 2, 1)) = 'H') THEN (T1."USED_TIME_INT") ELSE (0) END as "Hilfslohn",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2900')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Schulung intern",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5300')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Schulung extern",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1060')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Reifenwechsel",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5100  ','5110')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Krank",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('3020')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Arzt",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5000')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Urlaub",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5010')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Sonderurlaub",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5200')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Feiertag",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('4010')) THEN (T1."USED_TIME_INT") ELSE (0) END as "zu spät",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('4020')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Überstunden",
	   T5."ORDER_NUMBER" as "Order Number_Auftrag",
	   T5."CUSTOMER_GROUP" as "Customer Group",
	   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 ((left(T5."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END as "Umsatzart",
	   CASE WHEN ((substring(T1."ACTIVITY_CODE", 3, 2)) IN ('11','12','13')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Extern",
	   CASE WHEN ((substring(T1."ACTIVITY_CODE", 3, 2)) IN ('21','22','23')) THEN (T1."USED_TIME_INT") ELSE (0) END as "GWL",
	   CASE WHEN ((substring(T1."ACTIVITY_CODE", 3, 2)) IN ('31','32','33')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Intern",
	   (CASE WHEN ((substring(T1."ACTIVITY_CODE", 3, 2)) IN ('11','12','13')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN ((substring(T1."ACTIVITY_CODE", 3, 2)) IN ('21','22','23')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN ((substring(T1."ACTIVITY_CODE", 3, 2)) IN ('31','32','33')) THEN (T1."USED_TIME_INT") ELSE (0) END) as "produktiv",
	   (CASE WHEN (T1."ACTIVITY_CODE" IN ('1000','1002','1001','1003  ')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN ((substring(T1."ACTIVITY_CODE", 2, 1)) = 'L') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('W4  ','2200')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('W1  ','2210')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN ((substring(T1."ACTIVITY_CODE", 2, 1)) = 'H') 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 ('W3  ')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1060')) THEN (T1."USED_TIME_INT") ELSE (0) END) as "unproduktiv",
	   (CASE WHEN (T1."ACTIVITY_CODE" IN ('3020')) 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 ('3920')) 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 ('4020')) 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 ('5010')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5100  ','5110')) 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) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2999')) THEN (T1."USED_TIME_INT") ELSE (0) END) as "abwesend",
	   T3."WORK_LEADER_GROUP" + ' - ' + T3."WORKLEADER_TEXT" as "Monteur_Gruppe_ori",
	   ((T1."START_DATE_TIME")) as "Datum",
	   T6."CLIENT_DB" as "Hauptbetrieb",
	   T7."DEPARTMENT_TYPE_ID" as "Department Type Id",
	   T7."DESCRIPTION" as "Description",
	   (left(T2."SEL_DEPARTMENT",2)) as "Standort",
	   (substring(T2."SEL_DEPARTMENT", 4, 1)) as "Kostenstelle",
	   T2."SEL_NAME" as "Monteur_ori",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2999')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Konv Zeitkonto minus",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('3920')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Berufsschule",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('W3  ')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Ausb.Zeit mit Handwerker nur Azubi",
	   T6."WORK_LEADER_GROUP_ID" as "Work Leader Group Id",
	   '' as "Fahrten für KDD_",
	   '' as "MW tanken_",
	   '' as "Arbeiten Anlage B_",
	   '' as "Aushilfe Annahme_",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('996')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Abzug T390",
	   (T3."WORK_LEADER_GROUP" + ' - ' + T3."WORKLEADER_TEXT") as "Monteur_Gruppe",
	   T1."PROFILE_CODE" + ' - ' + T2."SEL_NAME" as "Monteur",
	   '' as "Aushilfe GW_",
	   T8."Zeitkategorie_1" as "Activity_Codes_Group1",
	   T8."Zeitkategorie_2" as "Activity_Codes_Group2",
	   T8."Activity_Code" + ' - ' + T8."Activity_Desc" as "Activity_Desc",
	   T9."Hauptbetrieb_ID" as "Hauptbetrieb Id",
	   T9."Hauptbetrieb_Name" as "Hauptbetrieb Name",
	   T9."Standort_ID" as "Standort Id",
	   T9."Standort_Name" as "Standort Name"
from "OPTIMA"."import"."EMPLOYEE" T6,
	"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"."import"."DEPARTMENT_TYPE" T7 on (T2."SEL_DEPARTMENT" = T7."DEPARTMENT_TYPE_ID") and (T2."CLIENT_DB" = T7."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Activity_Codes" T8 on (T4."CLIENT_DB" = T8."Client_DB") and (T4."ACTIVITY_CODE" = T8."Activity_Code")) left outer join "OPTIMA"."data"."GC_Department" T9 on (T2."CLIENT_DB" = T9."Hauptbetrieb") and ((left(T2."SEL_DEPARTMENT",2)) = T9."Standort"))
where ((T6."PERSON_ID" = T10."PERSON_ID") and (T6."CLIENT_DB" = T10."CLIENT_DB")) and ((T3."WORK_LEADER_GROUP" = T6."WORK_LEADER_GROUP_ID") and (T3."CLIENT_DB" = T6."CLIENT_DB"))
and (((not T1."PROFILE_CODE" IN ('MARE')) and ((((T1."START_DATE_TIME"))) >= convert(date, '2020-01-01'))) and (((T3."WORK_LEADER_GROUP" + ' - ' + T3."WORKLEADER_TEXT")) IN ('1100 - LH KDD Mechaniker','1700 - LH VW','2100 - SE KDD Mechaniker','2120 - SE KDD 2 Mechaniker','2700 - SE VW','3100 - LÜ KDD Mechaniker','3200 - LÜ KAR','3300 - LÜ LACK','3700 - LÜ VW','4100 - WE KDD Mechaniker','4700 - WE VW','6100 - DO KDD Mechaniker','6700 - DO VW')))
-- order by "Transact Date Long" asc
) D1