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

select "Employee No" as "Employee No",
	   "Datum" as "Datum",
	   "Department No" as "Department No",
	   "Time Account No" as "Time Account No",
	   "Time Account Value" as "Time Account Value",
	   "No" as "No",
	   "Description" as "Description",
	   "Description 2" as "Description 2",
	   "Zeitdauer" as "Zeitdauer",
	   RSUM("Time Account Value") as "Summe  (Zeitdauer) Nr.1",
	   "gesamt Stunden" as "gesamt Stunden",
	   RSUM("gesamt Stunden" for "Datum","Employee No") as "Summe  (gesamt Stunden) Nr.3",
	   RSUM("gesamt Stunden") as "Summe  (gesamt Stunden) Nr.2",
	   RSUM("gesamt Stunden") as "Summe  (gesamt Stunden) Nr.1",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "First Name" as "First Name",
	   "Last Name" as "Last Name",
	   "Monteur_Gruppe_ori_alt" as "Monteur_Gruppe_ori_alt",
	   "Abteilung" as "Abteilung",
	   "Monteur" as "Monteur",
	   "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",
	   "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",
	   "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",
	   "Kontrolle" as "Kontrolle",
	   "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_ori" as "Activity_Codes_Group2_ori",
	   "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",
	   "Filialcode_Employee" as "Filialcode_Employee"
from 
(select T1."Employee No_" as "Employee No",
	   T1."Current Date" as "Datum",
	   T1."Department No_" as "Department No",
	   T1."Time Account No_" as "Time Account No",
	   T1."Time Account Value" as "Time Account Value",
	   T2."No_" as "No",
	   T2."Description" as "Description",
	   T2."Description 2" as "Description 2",
	   T1."Time Account Value" as "Zeitdauer",
	   CASE WHEN (T2."No_" IN (100)) THEN (T1."Time Account Value") ELSE (0) END as "gesamt Stunden",
	   '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",
	   T4."Vorname" as "First Name",
	   T4."Nachname" as "Last Name",
	   T3."Group No_ 1" as "Monteur_Gruppe_ori_alt",
	   '' as "Abteilung",
	   T4."Vorname" + ' ' + T4."Nachname" as "Monteur",
	   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",
	   (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."Current Date" - cinterval(day(T1."Current Date") - 1))) as "Monatserster",
	   (convert(datetime, lastday((T1."Current Date")))) as "Monatsletzter",
	   (now()) as "Heute",
	   CASE WHEN (((now())) BETWEEN ((convert(datetime, T1."Current Date" - cinterval(day(T1."Current Date") - 1)))) AND ((convert(datetime, lastday((T1."Current Date")))))) THEN (T1."Current Date") ELSE null END as "Datum Tagesbericht",
	   T4."Anstellungsdatum" as "Employment Date",
	   T4."Global Dimension 1 Code" + ' - ' + T5."Name" as "Kostenstelle",
	   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."Group No_ 1" as "Monteur_Gruppe_ori",
	   T1."Time Account Value" - ((CASE WHEN (T2."No_" = 370) THEN (T1."Time Account Value") ELSE (0) END) + (CASE WHEN (T2."No_" = 379) THEN (T1."Time Account Value") ELSE (0) END) + (CASE WHEN (T2."No_" = 352) THEN (T1."Time Account Value") ELSE (0) END) + (CASE WHEN (T2."No_" = 354) THEN (T1."Time Account Value") ELSE (0) END) + 0 + (CASE WHEN (T2."No_" = 450) THEN (T1."Time Account Value") ELSE (0) END) + (CASE WHEN (T2."No_" = 355) THEN (T1."Time Account Value") ELSE (0) END) + (CASE WHEN (T2."No_" IN (350,351)) THEN (T1."Time Account Value") ELSE (0) END)) as "Kontrolle",
	   T1."Current 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",
	   (T4."Vorname" + ' ' + T4."Nachname") 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",
	   '' as "Order Number",
	   CASE WHEN (T1."Time Account No_" BETWEEN '300' AND '450') THEN ('abw.') ELSE null END as "Activity_Codes_Group1",
	   (left((cast_numberToString(cast_integer(T2."No_"))),3)) + ' - ' + T2."Description" as "Activity_Codes_Group2_ori",
	   CASE WHEN (((left((cast_numberToString(cast_integer(T2."No_"))),3)) + ' - ' + T2."Description") IN ('302 - Arztbesuch')) THEN ('ARZT - Arztbesuch') ELSE (((left((cast_numberToString(cast_integer(T2."No_"))),3)) + ' - ' + T2."Description")) END as "Activity_Codes_Group2",
	   (CASE WHEN (((left((cast_numberToString(cast_integer(T2."No_"))),3)) + ' - ' + T2."Description") IN ('302 - Arztbesuch')) THEN ('ARZT - Arztbesuch') ELSE (((left((cast_numberToString(cast_integer(T2."No_"))),3)) + ' - ' + T2."Description")) END) 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",
	   0 as "produktiv",
	   0 as "unproduktiv",
	   T1."Time Account Value" as "abwesend",
	   T4."Filialcode" as "Filialcode_Employee"
from "CARLO"."import"."Employee_T" T3,
	((("CARLO"."import"."Time_Entry_T" T1 left outer join "CARLO"."import"."Time_Account_T" T2 on (T1."Time Account No_" = (cast_numberToString(cast_integer(T2."No_")))) and (T1."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Employee" T4 on (T4."Nr_" = T1."Employee No_") and (T4."Client_DB" = T1."Client_DB")) 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 ((((((T2."No_" BETWEEN 299 AND 499) and (T1."Current Date" >= T4."Anstellungsdatum")) and (T1."Current Date" >= T4."Anstellungsdatum")) and ((od_year(T1."Current Date")) >= (od_year((now()))) - 2)) and (T3."Group No_ 1" IN ('AZUBI','MEISTER','MONTEUR','ANG','SB','VK'))) and ((CASE WHEN (((T4."Vorname" + ' ' + T4."Nachname") = 'Udo Litzinger') and (T1."Current Date" >= convert(datetime, '2018-01-01 00:00:00.000'))) THEN ('raus') ELSE ('rein') END) = 'rein'))
-- order by "Datum" asc,"Employee No" asc
) D1