C11/System/CARLO/IQD/Zeit/zeit_stempelzeiten_monteur_2_c11.sql

select "Entry No" as "Entry No",
	   "Employee No" as "Employee No",
	   "Date_ori" as "Date_ori",
	   "Time Value" as "Time Value",
	   "Begin End" as "Begin End",
	   "Cause Of Absence Code" as "Cause Of Absence Code",
	   "Terminal Code" as "Terminal Code",
	   "Department Code" as "Department Code",
	   "Make Code" as "Make Code",
	   "User Id" as "User Id",
	   "Reason Code" as "Reason Code",
	   "Sorting" as "Sorting",
	   "Posting Date" as "Posting Date",
	   "Order No" as "Order No",
	   "Service Job No" as "Service Job No",
	   "Closed" as "Closed",
	   "Task Type Code" as "Task Type Code",
	   "Link No" as "Link No",
	   "Task Ledger Entry No" as "Task Ledger Entry No",
	   "Corrected" as "Corrected",
	   "Starting Time" as "Starting Time",
	   "Ending Time" as "Ending Time",
	   "Duration" as "Duration",
	   "Time Acquisition Posted" as "Time Acquisition Posted",
	   "Branch Code" as "Branch Code",
	   "Approved By User Id" as "Approved By User Id",
	   "Approved" as "Approved",
	   "Automatic Posting" as "Automatic Posting",
	   "Subject To Approval" as "Subject To Approval",
	   "Applied-to Entry No" as "Applied-to Entry No",
	   "Ledger Entry Origin" as "Ledger Entry Origin",
	   "Correction Mode" as "Correction Mode",
	   "Posting Time" as "Posting Time",
	   "Service Advisor No" as "Service Advisor No",
	   "Resource Group No" as "Resource Group No",
	   "Tested" as "Tested",
	   "Location Code" as "Location Code",
	   "Leave" as "Leave",
	   "Zeitdauer" as "Zeitdauer",
	   RSUM("Duration") as "Summe  (Zeitdauer) Nr.1",
	   "Code" as "Code",
	   "Description_Task_Type" as "Description_Task_Type",
	   "Task Statistic Group" as "Task Statistic Group",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "No" as "No",
	   "Last Name" as "Last Name",
	   "First Name" as "First Name",
	   "Monteur" as "Monteur",
	   "Datum" as "Datum",
	   "Leaving Date" as "Leaving Date",
	   "Group No 1" as "Group No 1",
	   "Group No 2" as "Group No 2",
	   "Group No 3" as "Group No 3",
	   "Monteur_Gruppe_ori_alt" as "Monteur_Gruppe_ori_alt",
	   "Tage Heute Leaving Date" as "Tage Heute Leaving Date",
	   "Monteur_Gruppe" as "Monteur_Gruppe",
	   "produktiv/unproduktiv" as "produktiv/unproduktiv",
	   "Monatserster" as "Monatserster",
	   "Monatsletzter" as "Monatsletzter",
	   "Heute" as "Heute",
	   "Datum Tagesbericht" as "Datum Tagesbericht",
	   "Employment Date" as "Employment Date",
	   "Kostenstelle" as "Kostenstelle",
	   "unproduktiv_alt" as "unproduktiv_alt",
	   "abwesend_ori" as "abwesend_ori",
	   "Extern" as "Extern",
	   "GWL" as "GWL",
	   "Intern" as "Intern",
	   "Filialcode_Employee" as "Filialcode_Employee",
	   "Kostenstellencode_Employee" as "Kostenstellencode_Employee",
	   "Name_Kostenstelle" as "Name_Kostenstelle",
	   "Betrieb" as "Betrieb",
	   "Monteur_Gruppe_2" as "Monteur_Gruppe_2",
	   "Arbeitsvertragscode" as "Arbeitsvertragscode",
	   "Task Type Group" as "Task Type Group",
	   "Monteur_Gruppe_ori" as "Monteur_Gruppe_ori",
	   "Date" as "Date",
	   "Company_ID" as "Company_ID",
	   "Department_ID" as "Department_ID",
	   "Mechanic_Group2" as "Mechanic_Group2",
	   "Mechanic" as "Mechanic",
	   "Mechanic_Productive" as "Mechanic_Productive",
	   "Mechanic_Group1" as "Mechanic_Group1",
	   "Order Number" as "Order Number",
	   "Activity_Codes_Group1" as "Activity_Codes_Group1",
	   "Activity_Codes_Group2" as "Activity_Codes_Group2",
	   "Activity_Desc" as "Activity_Desc",
	   "Mechanic_Trainee" as "Mechanic_Trainee",
	   "produktiv" as "produktiv",
	   "unproduktiv" as "unproduktiv",
	   "abwesend" as "abwesend",
	   "TOY_produktiv_Zeit" as "TOY_produktiv_Zeit"
from 
(select T1."Entry No_" as "Entry No",
	   T1."Employee No_" as "Employee No",
	   T1."Date" as "Date_ori",
	   T1."Time Value" as "Time Value",
	   T1."Begin_End" as "Begin End",
	   T1."Cause of Absence Code" as "Cause Of Absence Code",
	   T1."Terminal Code" as "Terminal Code",
	   T1."Department Code" as "Department Code",
	   T1."Make Code" as "Make Code",
	   T1."User ID" as "User Id",
	   T1."Reason Code" as "Reason Code",
	   T1."Sorting" as "Sorting",
	   T1."Posting Date" as "Posting Date",
	   T1."Order No_" as "Order No",
	   T1."Service Job No_" as "Service Job No",
	   T1."Closed" as "Closed",
	   T1."Task Type Code" as "Task Type Code",
	   T1."Link No_" as "Link No",
	   T1."Task Ledger Entry No_" as "Task Ledger Entry No",
	   T1."Corrected" as "Corrected",
	   T1."Starting Time" as "Starting Time",
	   T1."Ending Time" as "Ending Time",
	   T1."Duration" as "Duration",
	   T1."Time Acquisition Posted" as "Time Acquisition Posted",
	   T1."Branch Code" as "Branch Code",
	   T1."Approved by User ID" as "Approved By User Id",
	   T1."Approved" as "Approved",
	   T1."Automatic Posting" as "Automatic Posting",
	   T1."Subject to Approval" as "Subject To Approval",
	   T1."Applied-to Entry No_" as "Applied-to Entry No",
	   T1."Ledger Entry Origin" as "Ledger Entry Origin",
	   T1."Correction Mode" as "Correction Mode",
	   T1."Posting Time" as "Posting Time",
	   T1."Service Advisor No_" as "Service Advisor No",
	   T1."Resource Group No_" as "Resource Group No",
	   T1."Tested" as "Tested",
	   T1."Location Code" as "Location Code",
	   T1."Leave" as "Leave",
	   T1."Duration" as "Zeitdauer",
	   T2."Code" as "Code",
	   T2."Description" as "Description_Task_Type",
	   T2."Task Statistic Group" as "Task Statistic Group",
	   '1' as "Hauptbetrieb",
	   CASE WHEN ((CASE WHEN ((((day((now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END) IN ('MONTEUR1','AZUBI 4','AZUBI 3','SPENG FL','AZUBI 1','AZUBI 2')) THEN ('00') WHEN ((CASE WHEN ((((day((now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END) = 'MONTEUR2') THEN ('02') WHEN ((T4."Filialcode" = '') and (not (CASE WHEN ((((day((now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END) IN ('MONTEUR1','AZUBI 4','AZUBI 3','SPENG FL','AZUBI 1','AZUBI 2','MONTEUR2'))) THEN ('00') ELSE (T4."Filialcode") END as "Standort",
	   T3."No_" as "No",
	   T3."Last Name" as "Last Name",
	   T3."First Name" as "First Name",
	   T3."First Name" + ' ' + T3."Last Name" as "Monteur",
	   T1."Date" as "Datum",
	   T3."Leaving Date" as "Leaving Date",
	   T3."Group No_ 1" as "Group No 1",
	   T3."Group No_ 2" as "Group No 2",
	   T3."Group No_ 3" as "Group No 3",
	   T3."Group No_ 1" as "Monteur_Gruppe_ori_alt",
	   (day((now()) - T3."Leaving Date")) as "Tage Heute Leaving Date",
	   CASE WHEN ((((day((now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END as "Monteur_Gruppe",
	   CASE WHEN (T4."Global Dimension 1 Code" IN ('40','41','44','45')) THEN ('prod. Personal') ELSE ('unprod. Personal') END as "produktiv/unproduktiv",
	   (convert(datetime, T1."Date" - cinterval(day(T1."Date") - 1))) as "Monatserster",
	   (convert(datetime, lastday((T1."Date")))) as "Monatsletzter",
	   (now()) as "Heute",
	   CASE WHEN (((now())) BETWEEN ((convert(datetime, T1."Date" - cinterval(day(T1."Date") - 1)))) AND ((convert(datetime, lastday((T1."Date")))))) THEN (T1."Date") ELSE null END as "Datum Tagesbericht",
	   T3."Employment Date" as "Employment Date",
	   T4."Global Dimension 1 Code" + ' - ' + T5."Name" as "Kostenstelle",
	   (CASE WHEN (T2."Code" IN ('W2')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('W6')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('W8')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('ABSCHLEPP')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('W1')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('HOLBRING')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('VERWALTUNG')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('SAMSTAG')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('KOA')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('EVENT')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('Z')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('W4')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('W3')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('B')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('ANW')) THEN (T1."Duration") ELSE (0) END) as "unproduktiv_alt",
	   (CASE WHEN (T2."Code" IN ('SCHUL-EXT')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('KINDKRANK','KRANK','KRANK O L')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('ARZT')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('URLAUB','U-HALBN','U-HALBV')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('URL-SONDER')) THEN (T1."Duration") ELSE (0) END) + 0 + (CASE WHEN (T2."Code" IN ('ÜSTD-AUSGL')) THEN (T1."Duration") ELSE (0) END) + (CASE WHEN (T2."Code" IN ('RAUCHEN')) THEN (T1."Duration") ELSE (0) END) as "abwesend_ori",
	   0 as "Extern",
	   0 as "GWL",
	   0 as "Intern",
	   T4."Filialcode" as "Filialcode_Employee",
	   T4."Global Dimension 1 Code" as "Kostenstellencode_Employee",
	   T5."Name" as "Name_Kostenstelle",
	   '00' as "Betrieb",
	   CASE WHEN (T4."Arbeitsvertragscode" IN ('AZUBI')) THEN ('AZUBI') WHEN (T4."Arbeitsvertragscode" IN ('MEIST')) THEN ('Meister') WHEN (T3."Task Type Group" = 'UNPROD') THEN ((CASE WHEN ((((day((now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END)) ELSE ('Monteur') END as "Monteur_Gruppe_2",
	   T4."Arbeitsvertragscode" as "Arbeitsvertragscode",
	   T3."Task Type Group" as "Task Type Group",
	   T3."Task Type Group" as "Monteur_Gruppe_ori",
	   T1."Date" as "Date",
	   '1' as "Company_ID",
	   '00' as "Department_ID",
	   (CASE WHEN (T4."Arbeitsvertragscode" IN ('AZUBI')) THEN ('AZUBI') WHEN (T4."Arbeitsvertragscode" IN ('MEIST')) THEN ('Meister') WHEN (T3."Task Type Group" = 'UNPROD') THEN ((CASE WHEN ((((day((now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END)) ELSE ('Monteur') END) as "Mechanic_Group2",
	   (T3."First Name" + ' ' + T3."Last Name") as "Mechanic",
	   CASE WHEN ((CASE WHEN (T4."Global Dimension 1 Code" IN ('40','41','44','45')) THEN ('prod. Personal') ELSE ('unprod. Personal') END) = 'prod. Personal') THEN ('produktiv') ELSE ('unproduktiv') END as "Mechanic_Productive",
	   (CASE WHEN ((((day((now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END) as "Mechanic_Group1",
	   T1."Order No_" as "Order Number",
	   CASE WHEN (T1."Task Type Code" IN ('AUFTRAG')) THEN ('produktiv') WHEN (T1."Task Type Code" IN ('ARZT','RAUCHEN','PAUSE')) THEN ('abwesend') WHEN (T1."Task Type Code" IN ('ANW','W1','W3','ABSCHLEPP','SERVICETAG','DIENSTGANG','W6','W8','HILF1','CHEFARBEIT','AU','W2','HILF7','HILF5','KURZ','DIAGNOSE','HILF3','HILF6','HILF2')) THEN ('unproduktiv') ELSE null END as "Activity_Codes_Group1",
	   T1."Task Type Code" + ' - ' + T2."Description" as "Activity_Codes_Group2",
	   (T1."Task Type Code" + ' - ' + T2."Description") as "Activity_Desc",
	   CASE WHEN ((CASE WHEN (T4."Arbeitsvertragscode" IN ('AZUBI')) THEN ('AZUBI') WHEN (T4."Arbeitsvertragscode" IN ('MEIST')) THEN ('Meister') WHEN (T3."Task Type Group" = 'UNPROD') THEN ((CASE WHEN ((((day((now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END)) ELSE ('Monteur') END) IN ('AZUBI')) THEN ('Azubi') ELSE ('Monteur') END as "Mechanic_Trainee",
	   CASE WHEN ((CASE WHEN (T1."Task Type Code" IN ('AUFTRAG')) THEN ('produktiv') WHEN (T1."Task Type Code" IN ('ARZT','RAUCHEN','PAUSE')) THEN ('abwesend') WHEN (T1."Task Type Code" IN ('ANW','W1','W3','ABSCHLEPP','SERVICETAG','DIENSTGANG','W6','W8','HILF1','CHEFARBEIT','AU','W2','HILF7','HILF5','KURZ','DIAGNOSE','HILF3','HILF6','HILF2')) THEN ('unproduktiv') ELSE null END) = 'produktiv') THEN (T1."Duration") ELSE (0) END as "produktiv",
	   CASE WHEN ((CASE WHEN (T1."Task Type Code" IN ('AUFTRAG')) THEN ('produktiv') WHEN (T1."Task Type Code" IN ('ARZT','RAUCHEN','PAUSE')) THEN ('abwesend') WHEN (T1."Task Type Code" IN ('ANW','W1','W3','ABSCHLEPP','SERVICETAG','DIENSTGANG','W6','W8','HILF1','CHEFARBEIT','AU','W2','HILF7','HILF5','KURZ','DIAGNOSE','HILF3','HILF6','HILF2')) THEN ('unproduktiv') ELSE null END) = 'unproduktiv') THEN (T1."Duration") ELSE (0) END as "unproduktiv",
	   CASE WHEN ((CASE WHEN (T1."Task Type Code" IN ('AUFTRAG')) THEN ('produktiv') WHEN (T1."Task Type Code" IN ('ARZT','RAUCHEN','PAUSE')) THEN ('abwesend') WHEN (T1."Task Type Code" IN ('ANW','W1','W3','ABSCHLEPP','SERVICETAG','DIENSTGANG','W6','W8','HILF1','CHEFARBEIT','AU','W2','HILF7','HILF5','KURZ','DIAGNOSE','HILF3','HILF6','HILF2')) THEN ('unproduktiv') ELSE null END) = 'abwesend') THEN (T1."Duration") ELSE (0) END as "abwesend",
	   CASE WHEN (T2."Code" = 'TOY') THEN (T1."Duration") ELSE (0) END as "TOY_produktiv_Zeit"
from (("CARLO"."import"."Task_Acquisition_Ledger_Entry" T1 left outer join "CARLO"."import"."Task_Type" T2 on (T1."Task Type Code" = T2."Code") and (T1."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Employee_T" T3 on (T3."No_" = T1."Employee No_") and (T3."Client_DB" = T1."Client_DB")),
	("CARLO"."import"."Employee" T4 left outer join "CARLO"."import"."Kostenstelle" T5 on (T4."Global Dimension 1 Code" = T5."Code") and (T4."Client_DB" = T5."Client_DB"))
where ((T4."Nr_" = T3."No_") and (T4."Client_DB" = T3."Client_DB"))
and (((((((T1."Sorting" = 1) and (T1."Corrected" = 0)) and (T1."Date" >= T3."Employment Date")) and ((od_year(T1."Date")) >= (od_year((now()))) - 2)) and (T3."Group No_ 1" IN ('AZUBI','MEISTER','MONTEUR','ANG','SB','VK'))) and (not T1."Task Type Code" IN ('PAUSE'))) and ((CASE WHEN (((T3."First Name" + ' ' + T3."Last Name") = 'Udo Litzinger') and (T1."Date" >= convert(datetime, '2018-01-01 00:00:00.000'))) THEN ('raus') ELSE ('rein') END) = 'rein'))
-- order by "Employee No" asc,"Date_ori" asc
) D1