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",
	   RSUM("Anwesenheit") as "Summe  (Anwesenheit) Nr.1",
	   "Nacharbeit" as "Nacharbeit",
	   "Leerlauf/Wartezeit" as "Leerlauf/Wartezeit",
	   "Hol u Bring Service" as "Hol u Bring Service",
	   "Instandhltg. Werkstatt" as "Instandhltg. Werkstatt",
	   "Unprod. Anwes." as "Unprod. Anwes.",
	   "Hilfslohn" as "Hilfslohn",
	   "Schulung intern" as "Schulung intern",
	   "Schulung extern" as "Schulung extern",
	   "Reifenlager" as "Reifenlager",
	   "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",
	   "GW-Bewertung" as "GW-Bewertung",
	   "Waschanlage" as "Waschanlage",
	   "Sondereinsatz lt. WL" as "Sondereinsatz lt. WL",
	   "Abschleppen" as "Abschleppen",
	   "Abzug T390" as "Abzug T390",
	   "Monteur_Gruppe" as "Monteur_Gruppe",
	   "Monteur" as "Monteur",
	   "Tag beenden" as "Tag beenden",
	   "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 ('2830')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Nacharbeit",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2000')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Leerlauf/Wartezeit",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('HUB ')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Hol u Bring Service",
	   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 ('2100','1001','1000','1005','1004','2110')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Unprod. Anwes.",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2200','2210','2220','2250','2230')) 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 ('5100')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Schulung extern",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2800')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Reifenlager",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5000  ')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Krank",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('3000')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Arzt",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5200')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Urlaub",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5210')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Sonderurlaub",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5300')) 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 ('5400')) 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 (T6."Zeitkategorie_2" IN ('Extern')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Extern",
	   CASE WHEN (T6."Zeitkategorie_2" IN ('GWL')) THEN (T1."USED_TIME_INT") ELSE (0) END as "GWL",
	   CASE WHEN (T6."Zeitkategorie_2" IN ('Intern')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Intern",
	   (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 "produktiv",
	   CASE WHEN (T6."Zeitkategorie_1" IN ('unproduktiv')) THEN (T1."USED_TIME_INT") ELSE (0) END as "unproduktiv",
	   CASE WHEN (T6."Zeitkategorie_1" IN ('Abwesenheit')) 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",
	   T7."CLIENT_DB" as "Hauptbetrieb",
	   T8."DEPARTMENT_TYPE_ID" as "Department Type Id",
	   T8."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",
	   '' as "Ausb.Zeit mit Handwerker nur Azubi_",
	   T7."WORK_LEADER_GROUP_ID" as "Work Leader Group Id",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2850')) THEN (T1."USED_TIME_INT") ELSE (0) END as "GW-Bewertung",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2810')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Waschanlage",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2820')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Sondereinsatz lt. WL",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2840')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Abschleppen",
	   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",
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1009')) THEN (T1."USED_TIME_INT") ELSE (0) END as "Tag beenden",
	   T6."Zeitkategorie_1" as "Activity_Codes_Group1",
	   T6."Zeitkategorie_2" as "Activity_Codes_Group2",
	   T6."Activity_Code" + ' - ' + T6."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" 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 ((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 ((((T1."START_DATE_TIME"))) >= convert(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 "Transact Date Long" asc
) D1