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

select "Employee No" as "Employee No",
	   "Current Date" as "Current Date",
	   "Department No" as "Department No",
	   "Order No" as "Order No",
	   "Task No" as "Task No",
	   "Dimension 1" as "Dimension 1",
	   "Dimension 2" as "Dimension 2",
	   "Dimension 3" as "Dimension 3",
	   "Dimension 4" as "Dimension 4",
	   "Time Account No" as "Time Account No",
	   "Time Account Value" as "Time Account Value",
	   "Zeit" as "Zeit",
	   RSUM("Time Account Value" for "Employee No","Time Account No") as "Summe  (Zeit) Nr.3",
	   RSUM("Time Account Value" for "Employee No","Time Account No") as "Summe  (Zeit) Nr.2",
	   RSUM("Time Account Value" for "Employee No","Time Account No") as "Summe  (Zeit) Nr.1",
	   "Datum" as "Datum",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Filialcode_Monteur" as "Filialcode_Monteur",
	   "Standort" as "Standort",
	   "Betrieb" as "Betrieb",
	   "Kostenstellencode_Monteur" as "Kostenstellencode_Monteur",
	   "Monteur_Gruppe_ori_alt" as "Monteur_Gruppe_ori_alt",
	   "Leaving Date" as "Leaving Date",
	   "Tage Heute Leaving Date" as "Tage Heute Leaving Date",
	   "Monteur_Gruppe" as "Monteur_Gruppe",
	   "Nr_Monteur" as "Nr_Monteur",
	   "Monteur_Gruppe_2" as "Monteur_Gruppe_2",
	   "Vorname_Monteur" as "Vorname_Monteur",
	   "Nachname_Monteur" as "Nachname_Monteur",
	   "Monteur" as "Monteur",
	   "Name_Kostenstelle_Monteur" as "Name_Kostenstelle_Monteur",
	   "Kostenstelle" as "Kostenstelle",
	   "benutzte Zeit" as "benutzte Zeit",
	   RSUM("benutzte Zeit" for "Employee No","Time Account No") as "Summe  (benutzte Zeit) Nr.2",
	   RSUM("benutzte Zeit" for "Employee No","Time Account No") as "Summe  (benutzte Zeit) Nr.1",
	   "verr. Zeit" as "verr. Zeit",
	   "ben. Zeit gerundet" as "ben. Zeit gerundet",
	   RSUM("ben. Zeit gerundet" for "Employee No","Time Account No") as "Summe  (ben. Zeit gerundet) Nr.2",
	   RSUM("ben. Zeit gerundet" for "Employee No","Time Account No") as "Summe  (ben. Zeit gerundet) Nr.1",
	   "Description" as "Description",
	   "Anwesenheit" as "Anwesenheit",
	   "produktiv" as "produktiv",
	   "Arbeitsvertragscode" as "Arbeitsvertragscode",
	   "Pause" as "Pause",
	   "Sollzeit" as "Sollzeit",
	   "Anwes. Netto" as "Anwes. Netto",
	   "Task Type Group" as "Task Type Group",
	   "Group No 1" as "Group No 1",
	   "Group No 3" as "Group No 3",
	   "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",
	   "Mechanic_Trainee" as "Mechanic_Trainee",
	   "Invoiced_Time" as "Invoiced_Time",
	   "Used_Time_Order" as "Used_Time_Order"
from 
(select T1."Employee No_" as "Employee No",
	   T1."Current Date" as "Current Date",
	   T1."Department No_" as "Department No",
	   T1."Order No_" as "Order No",
	   T1."Task No_" as "Task No",
	   T1."Dimension 1" as "Dimension 1",
	   T1."Dimension 2" as "Dimension 2",
	   T1."Dimension 3" as "Dimension 3",
	   T1."Dimension 4" as "Dimension 4",
	   T1."Time Account No_" as "Time Account No",
	   T1."Time Account Value" as "Time Account Value",
	   T1."Time Account Value" as "Zeit",
	   T1."Current Date" as "Datum",
	   '1' as "Hauptbetrieb",
	   T2."Filialcode" as "Filialcode_Monteur",
	   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",
	   '00' as "Betrieb",
	   T2."Global Dimension 1 Code" as "Kostenstellencode_Monteur",
	   T3."Group No_ 1" as "Monteur_Gruppe_ori_alt",
	   T3."Leaving Date" as "Leaving Date",
	   (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",
	   T2."Nr_" as "Nr_Monteur",
	   CASE WHEN (T2."Arbeitsvertragscode" IN ('AZUBI')) THEN ('AZUBI') WHEN (T2."Arbeitsvertragscode" IN ('MEIST')) THEN ('Meister') ELSE ('Monteur') END as "Monteur_Gruppe_2",
	   T2."Vorname" as "Vorname_Monteur",
	   T2."Nachname" as "Nachname_Monteur",
	   T2."Vorname" + ' ' + T2."Nachname" as "Monteur",
	   T5."Name" as "Name_Kostenstelle_Monteur",
	   T2."Global Dimension 1 Code" + ' - ' + T5."Name" as "Kostenstelle",
	   CASE WHEN (T1."Time Account No_" IN ('6121','6122','6123')) THEN (T1."Time Account Value") ELSE (0) END as "benutzte Zeit",
	   CASE WHEN (T1."Time Account No_" IN ('6201','6202','6203')) THEN (T1."Time Account Value") ELSE (0) END as "verr. Zeit",
	   (round((CASE WHEN (T1."Time Account No_" IN ('6121','6122','6123')) THEN (T1."Time Account Value") ELSE (0) END),2,0)) as "ben. Zeit gerundet",
	   T6."Description" as "Description",
	   CASE WHEN (T1."Time Account No_" = '100') THEN (T1."Time Account Value") ELSE (0) END as "Anwesenheit",
	   (CASE WHEN (T1."Time Account No_" IN ('6121','6122','6123')) THEN (T1."Time Account Value") ELSE (0) END) as "produktiv",
	   T2."Arbeitsvertragscode" as "Arbeitsvertragscode",
	   CASE WHEN (T1."Time Account No_" = '1300') THEN (T1."Time Account Value") ELSE (0) END as "Pause",
	   CASE WHEN (T1."Time Account No_" = '1200') THEN (T1."Time Account Value") ELSE (0) END as "Sollzeit",
	   (CASE WHEN (T1."Time Account No_" = '100') THEN (T1."Time Account Value") ELSE (0) END) - (CASE WHEN (T1."Time Account No_" = '1300') THEN (T1."Time Account Value") ELSE (0) END) as "Anwes. Netto",
	   T3."Task Type Group" as "Task Type Group",
	   T3."Group No_ 1" as "Group No 1",
	   T3."Group No_ 3" as "Group No 3",
	   T3."Group No_ 1" as "Monteur_Gruppe_ori",
	   T1."Current Date" as "Date",
	   '1' as "Company_ID",
	   '00' as "Department_ID",
	   (CASE WHEN (T2."Arbeitsvertragscode" IN ('AZUBI')) THEN ('AZUBI') WHEN (T2."Arbeitsvertragscode" IN ('MEIST')) THEN ('Meister') ELSE ('Monteur') END) as "Mechanic_Group2",
	   (T2."Vorname" + ' ' + T2."Nachname") as "Mechanic",
	   ('prod. Personal') 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 ((CASE WHEN (T2."Arbeitsvertragscode" IN ('AZUBI')) THEN ('AZUBI') WHEN (T2."Arbeitsvertragscode" IN ('MEIST')) THEN ('Meister') ELSE ('Monteur') END) IN ('AZUBI')) THEN ('Azubi') ELSE ('Monteur') END as "Mechanic_Trainee",
	   (CASE WHEN (T1."Time Account No_" IN ('6201','6202','6203')) THEN (T1."Time Account Value") ELSE (0) END) as "Invoiced_Time",
	   (CASE WHEN (T1."Time Account No_" IN ('6121','6122','6123')) THEN (T1."Time Account Value") ELSE (0) END) as "Used_Time_Order"
from (((("CARLO"."import"."Archiv_Werkstattkopf" T7 left outer join (("CARLO"."import"."Time_Entry_T" T1 left outer join "CARLO"."import"."Employee" T2 on (T2."Nr_" = T1."Employee No_") and (T2."Client_DB" = T1."Client_DB")) left outer join "CARLO"."import"."Employee_T" T3 on (T2."Nr_" = T3."No_") and (T2."Client_DB" = T3."Client_DB")) on (T1."Order No_" = T7."Nr_") and (T1."Client_DB" = T7."Client_DB")) left outer join "CARLO"."import"."Employee" T4 on (T4."Nr_" = T7."Serviceberaternr_") and (T4."Client_DB" = T7."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T5 on (T2."Global Dimension 1 Code" = T5."Code") and (T2."Client_DB" = T5."Client_DB")) left outer join "CARLO"."import"."Time_Account_T" T6 on (T1."Time Account No_" = (cast_numberToString(cast_integer(T6."No_")))) and (T1."Client_DB" = T6."Client_DB"))
where (((od_year(T1."Current Date")) >= (od_year((now()))) - 2) and (T1."Time Account No_" IN ('6121','6122','6123','6201','6202','6203')))
-- order by "Employee No" asc,"Time Account No" asc
) D1