C11/System/NAVISION/IQD/Zeiten/zeit_mitarbeiter_ueberstunden_fuer_zeit.sql

select T1."No_" as "No",
	   T1."ID No_" as "Id No",
	   T1."Name" as "Name",
	   T1."Last Name" as "Last Name",
	   T1."First Name" as "First Name",
	   T1."Employment Date" as "Employment Date",
	   T1."Leaving Date" as "Leaving Date",
	   T1."Department No_" as "Department No_2",
	   T1."Group No_ 1" as "Group No 1",
	   T1."Group No_ 2" as "Group No 2",
	   T1."Group No_ 3" as "Group No 3",
	   T1."Time Object No_" as "Time Object No",
	   T2."Employee No_" as "Employee No",
	   T2."Current Date" as "Datum",
	   T2."Department No_" as "Department No",
	   T2."Order No_" as "Order No",
	   T2."Time Account No_" as "Time Account No",
	   (convert(float, T2."Time Account Value")) as "Time Account Value",
	   '1' as "Hauptbetrieb",
	   CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END as "Standort",
	   T1."Pay Group No_" as "Monteur_Gruppe_ori",
	   (getdate()) - 1 as "Heute",
	   (-1 * datediff(day, (getdate()), T1."Leaving Date")) as "Tage Heute Leaving Date",
	   CASE WHEN ((((-1 * datediff(day, (getdate()), T1."Leaving Date"))) > 20) and (T1."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('augetretene Mitarbeiter') ELSE (T1."Pay Group No_") END as "Monteur_Gruppe",
	   CASE WHEN (T1."Task Type Group" IN ('MONTEURE','PROD MEIST')) THEN ('prod. Personal') WHEN (T1."Task Type Group" IN ('VERWALTUNG')) THEN ('unprod. Personal') ELSE null END as "produktiv/unproduktiv",
	   (convert(datetime, T2."Current Date" - cinterval(-1 * datediff(day, T2."Current Date"), 1))) as "Monatserster",
	   (convert(datetime, eomonth((T2."Current Date")))) as "Monatsletzter",
	   CASE WHEN (((getdate()) - 1) BETWEEN ((convert(datetime, T2."Current Date" - cinterval(-1 * datediff(day, T2."Current Date"), 1)))) AND ((convert(datetime, eomonth((T2."Current Date")))))) THEN (T2."Current Date") ELSE null END as "Datum Tagesbericht",
	   '' as "Order Number",
	   CASE WHEN (T1."Last Name" IN ('Schmid','Wittenberg')) THEN (T1."First Name" + ' ' + T1."Last Name" + '_' + '1') ELSE (T1."First Name" + ' ' + T1."Last Name") END as "Monteur",
	   CASE WHEN (T2."Time Account No_" IN ('1101')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Überstunden",
	   (month(T2."Current Date")) as "Monat_Datum",
	   (month((getdate()))) as "Monat_Jetzt",
	   ((month((getdate())))) - ((month(T2."Current Date"))) as "Diff_Monat",
	   (year((getdate()))) as "Jahr_Jetzt",
	   (year(T2."Current Date")) as "Jahr_Datum",
	   ((year((getdate())))) - ((year(T2."Current Date"))) as "Diff_Jahr",
	   T1."Client_DB" as "Hauptbetrieb_ID",
	   CASE WHEN (T1."Client_DB" = '1') THEN ('AHR') WHEN (T1."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   (CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END) as "Standort_ID",
	   CASE WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN ((left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((left(T1."Department No_",2))) END)) IN ('80')) THEN ('WTB') ELSE null END as "Standort_Name"
from "NAVISION"."import"."Employee_T" T1,
	"NAVISION"."import"."Time_Entry_T" T2
where ((T1."No_" = T2."Employee No_") and (T1."Client_DB" = T2."Client_DB"))
and (((((((((T2."Current Date" >= T1."Employment Date") and ((T1."Leaving Date" >= (getdate())) or (T1."Leaving Date" = convert(datetime, '1753-01-01 00:00:00.000')))) and (T2."Current Date" >= convert(datetime, '2021-01-01 00:00:00.000'))) and (T2."Time Account No_" IN ('1101','3500','1090'))) and (not T1."No_" IN ('11724','65503','93304'))) and ((CASE WHEN (T1."Task Type Group" IN ('MONTEURE','PROD MEIST')) THEN ('prod. Personal') WHEN (T1."Task Type Group" IN ('VERWALTUNG')) THEN ('unprod. Personal') ELSE null END) = 'prod. Personal')) and ((CASE WHEN (T2."Time Account No_" IN ('1101')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END) <> 0)) and (((((month((getdate())))) - ((month(T2."Current Date")))) = 1) or ((((month((getdate())))) - ((month(T2."Current Date")))) = -11))) and ((((year((getdate())))) - ((year(T2."Current Date")))) <= 1))
-- order by "Datum" asc,"Name" asc,"Time Account No" asc