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

select T1."No_" as "No_2",
	   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",
	   (convert(float, T1."Std_ Vac_ Entitlem_ Days")) as "Std Vac Entitlem Days",
	   (convert(float, T1."Handic_ Vac_ Entitlem_ Days")) as "Handic Vac Entitlem Days",
	   (convert(float, T1."Spec_ Vac_2 Entitlem_ Days")) as "Spec Vac 2 Entitlem Days",
	   (convert(float, T1."Spec_ Vac_3 Entitlem_ Days")) as "Spec Vac 3 Entitlem Days",
	   (convert(float, T1."Std_Vac_Days Car_Fwd_Prev_Y_")) as "Std Vac Days Car Fwd Prev Y",
	   (convert(float, T1."Handic_ Vac_1 Days Car_ Fwd_")) as "Handic Vac 1 Days Car Fwd",
	   (convert(float, T1."Special Vac_2 Days Car_ Fwd_")) as "Special Vac 2 Days Car Fwd",
	   (convert(float, T1."Special Vac_3 Days Car_ Fwd_")) as "Special Vac 3 Days Car Fwd",
	   (convert(float, T1."Rem_ Vac_ Days taken")) as "Rem Vac Days Taken",
	   (convert(float, T1."Rem_ Vac_ Days planned")) as "Rem Vac Days Planned",
	   (convert(float, T1."Standard Vac_ Entitlement Hrs_")) as "Standard Vac Entitlement Hrs",
	   (convert(float, T1."Handic_ Vac_1 Entitlement Hrs_")) as "Handic Vac 1 Entitlement Hrs",
	   (convert(float, T1."Special Vac_2 Entitlement (H)")) as "Special Vac 2 Entitlement (h)",
	   (convert(float, T1."Std_ Vac_Car_Fwd_Prev_Y_(H)")) as "Std Vac Car Fwd Prev Y (h)",
	   (convert(float, T1."Handic_ Vac_1 Hours Car_ Fwd_")) as "Handic Vac 1 Hours Car Fwd",
	   (convert(float, T1."Spec_ Vac_2 Hours Car_ Fwd_")) as "Spec Vac 2 Hours Car Fwd",
	   (convert(float, T1."Rem_ Vac_ Hours taken")) as "Rem Vac Hours Taken",
	   (convert(float, T1."Rem_ Vac_ Hrs_ Planned")) as "Rem Vac Hrs Planned",
	   (convert(float, T1."Rem_ Handic_ Vac_ Days taken")) as "Rem Handic Vac Days Taken",
	   (convert(float, T1."Rem_ Handic_ Vac_ Days planned")) as "Rem Handic Vac Days Planned",
	   T1."Values Car_ Fwd_ Inactive" as "Values Car Fwd Inactive",
	   T1."Present" as "Present",
	   T1."Absent" as "Absent",
	   T1."Department Code" as "Department Code",
	   T1."Task Type Group" as "Task Type Group",
	   T1."Efficiency %" as "Efficiency %",
	   T1."Resource No_" as "Resource No",
	   T1."Rating PEREAS" as "Rating Pereas",
	   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",
	   T2."TA Class (General)" as "Ta Class (general)",
	   T2."TA Class (Statistics)" as "Ta Class (statistics)",
	   T2."TA Class (Absent Days)" as "Ta Class (absent Days)",
	   T2."TA Class (Time Processing)" as "Ta Class (time Processing)",
	   T2."TA Class (Individual)" as "Ta Class (individual)",
	   T2."TA Class (Employee Info)" as "Ta Class (employee Info)",
	   T2."TA Class (Vacation Reduction)" as "Ta Class (vacation Reduction)",
	   T2."TA Formatting" as "Ta Formatting",
	   T2."Generating Function" as "Generating Function",
	   T2."Record protected" as "Record Protected",
	   T3."No_" as "No",
	   T3."Description" as "Description",
	   T3."Description 2" as "Description 2",
	   T3."Symbol" as "Symbol",
	   T3."Character" as "Character",
	   T3."Pay Type 1" as "Pay Type 1",
	   T2."Client_DB" 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"))) > 0) 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",
	   T1."First Name" + ' ' + T1."Last Name" as "Monteur",
	   CASE WHEN (T2."Time Account No_" IN ('3500')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "ausbezahlte Überstunden",
	   CASE WHEN (T2."Time Account No_" IN ('1090')) THEN (((convert(float, T2."Time Account Value")))) ELSE (0) END as "Überstunden",
	   T2."Client_DB" as "Hauptbetrieb_ID",
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."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,
	"NAVISION"."import"."Time_Account_T" T3
where ((T1."No_" = T2."Employee No_") and (T1."Client_DB" = T2."Client_DB")) and ((T2."Time Account No_" = (((T3."No_")))) and (T3."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, (getdate()) - cinterval(-1 * datediff(day, (getdate())), 1))))) and (T2."Time Account No_" IN ('3500'))) and (T1."No_" <> '11724'))
-- order by "Datum" asc,"Name" asc,"Time Account No" asc