C11/System/NAVISION/IQD/Zeiten/zeit_mitarbeiter_urlaub.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",
	   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",
	   ((convert(float, T1."Std_ Vac_ Entitlem_ Days"))) as "Urlaubsanspruch",
	   ((convert(float, T1."Std_Vac_Days Car_Fwd_Prev_Y_"))) as "Urlaubsübertrag Vorjahr",
	   (((convert(float, T1."Std_ Vac_ Entitlem_ Days")))) + (((convert(float, T1."Std_Vac_Days Car_Fwd_Prev_Y_")))) as "Gesamturlaubsanspruch",
	   ((convert(float, T1."Rem_ Vac_ Days taken"))) as "Resturlaub",
	   ((((convert(float, T1."Std_ Vac_ Entitlem_ Days")))) + (((convert(float, T1."Std_Vac_Days Car_Fwd_Prev_Y_"))))) - (((convert(float, T1."Rem_ Vac_ Days taken")))) + ((convert(float, T1."Spec_ Vac_2 Entitlem_ Days"))) as "bisher genommener Urlaub",
	   ((convert(float, T1."Rem_ Vac_ Days planned"))) as "Resturlaub bis Jahresende",
	   (((convert(float, T1."Rem_ Vac_ Days taken")))) - (((convert(float, T1."Rem_ Vac_ Days planned")))) as "geplanter Urlaub",
	   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",
	   T1."First Name" + ' ' + T1."Last Name" as "Monteur",
	   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",
	   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
where ((T1."No_" = T2."Employee No_") and (T1."Client_DB" = T2."Client_DB"))
and (((T1."Leaving Date" >= (getdate())) or (T1."Leaving Date" = convert(datetime, '1753-01-01 00:00:00.000'))) and (T1."No_" <> '11724'))
-- order by "No" asc