C11/System/LOCOSOFT/IQD/Zeit/Zeit_Abwesenheit_neu.sql

select "Employee Number_Employees" as "Employee Number_Employees",
	   "Name_Employees" as "Name_Employees",
	   "Initials_Employees" as "Initials_Employees",
	   "Mechanic Number_Employees" as "Mechanic Number_Employees",
	   "Salesman Number_Employees" as "Salesman Number_Employees",
	   "Employment Date_Employees" as "Employment Date_Employees",
	   "Termination Date_Employees" as "Termination Date_Employees",
	   "Leave Date_Employees" as "Leave Date_Employees",
	   "Employee Number_Absence_Cal" as "Employee Number_Absence_Cal",
	   "Date_Absence_Cal" as "Date_Absence_Cal",
	   "Type_Absence_Cal" as "Type_Absence_Cal",
	   "Is Payed_Absence_Cal" as "Is Payed_Absence_Cal",
	   "Day Contingent_Absence_Cal" as "Day Contingent_Absence_Cal",
	   "Reason_Absence_Cal" as "Reason_Absence_Cal",
	   "Booking Flag_Absence_Cal" as "Booking Flag_Absence_Cal",
	   "Id_Absence_Reas" as "Id_Absence_Reas",
	   "Description_Absence_Reas" as "Description_Absence_Reas",
	   "Is Annual Vacation_Absence_Reas" as "Is Annual Vacation_Absence_Reas",
	   "Type_Absence_Types" as "Type_Absence_Types",
	   "Description_Absence_Types" as "Description_Absence_Types",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "Monteur" as "Monteur",
	   "Dummy_Arbeitszeit_Tag" as "Dummy_Arbeitszeit_Tag",
	   "Abwesenheit" as "Abwesenheit",
	   "Zeitkategorie" as "Zeitkategorie",
	   "Zeitkategorie2" as "Zeitkategorie2",
	   "Ges. Std." as "Ges. Std.",
	   "Datum" as "Datum",
	   "Order Number" as "Order Number",
	   "Wochentag_Datum" as "Wochentag_Datum",
	   "Validity Date_Worktimes" as "Validity Date_Worktimes",
	   "Dayofweek_Worktimes" as "Dayofweek_Worktimes",
	   "Work Duration_Worktimes" as "Work Duration_Worktimes",
	   "Worktime Start_Worktimes" as "Worktime Start_Worktimes",
	   "Worktime End_Worktimes" as "Worktime End_Worktimes",
	   MAX("Validity Date_Worktimes") OVER (partition by "Employee Number_Employees") as "Max_Validity_Date_Worktimes",
	   "Arbeitszeit_Tag" as "Arbeitszeit_Tag"
from 
(select "Employee Number_Employees",
	   "Name_Employees",
	   "Initials_Employees",
	   "Mechanic Number_Employees",
	   "Salesman Number_Employees",
	   "Employment Date_Employees",
	   "Termination Date_Employees",
	   "Leave Date_Employees",
	   "Employee Number_Absence_Cal",
	   "Date_Absence_Cal",
	   "Type_Absence_Cal",
	   "Is Payed_Absence_Cal",
	   "Day Contingent_Absence_Cal",
	   "Reason_Absence_Cal",
	   "Booking Flag_Absence_Cal",
	   "Id_Absence_Reas",
	   "Description_Absence_Reas",
	   "Is Annual Vacation_Absence_Reas",
	   "Type_Absence_Types",
	   "Description_Absence_Types",
	   '1' as "Hauptbetrieb",
	   "Standort",
	   "Monteur",
	   8 as "Dummy_Arbeitszeit_Tag",
	   "Abwesenheit",
	   'Abwesenheit' as "Zeitkategorie",
	   "Zeitkategorie2",
	   "Abwesenheit" as "Ges. Std.",
	   "Date_Absence_Cal" as "Datum",
	   '' as "Order Number",
	   "Wochentag_Datum",
	   "Validity Date_Worktimes",
	   "Dayofweek_Worktimes",
	   "Work Duration_Worktimes",
	   "Worktime Start_Worktimes",
	   "Worktime End_Worktimes",
	   "Work Duration_Worktimes" as "Arbeitszeit_Tag",
	   MAX("Validity Date_Worktimes") OVER (partition by "Employee Number_Employees") as c39
from 
(select T1."employee_number" as "Employee Number_Employees",
	   T5."work_duration" as "Work Duration_Worktimes",
	   T5."worktime_end" as "Worktime End_Worktimes",
	   T5."worktime_start" as "Worktime Start_Worktimes",
	   T5."dayofweek" as "Dayofweek_Worktimes",
	   T5."validity_date" as "Validity Date_Worktimes",
	   (datepart(weekday, T2."date")) as "Wochentag_Datum",
	   T2."date" as "Date_Absence_Cal",
	   (T2."day_contingent" * T5."work_duration") as "Abwesenheit",
	   T3."id" + ' - ' + T3."description" as "Zeitkategorie2",
	   (rtrim((((T1."employee_number"))))) + ' - ' + T1."name" as "Monteur",
	   '0' + (rtrim((((T1."subsidiary"))))) as "Standort",
	   T4."description" as "Description_Absence_Types",
	   T4."type" as "Type_Absence_Types",
	   T3."is_annual_vacation" as "Is Annual Vacation_Absence_Reas",
	   T3."description" as "Description_Absence_Reas",
	   T3."id" as "Id_Absence_Reas",
	   T2."booking_flag" as "Booking Flag_Absence_Cal",
	   T2."reason" as "Reason_Absence_Cal",
	   T2."day_contingent" as "Day Contingent_Absence_Cal",
	   T2."is_payed" as "Is Payed_Absence_Cal",
	   T2."type" as "Type_Absence_Cal",
	   T2."employee_number" as "Employee Number_Absence_Cal",
	   T1."leave_date" as "Leave Date_Employees",
	   T1."termination_date" as "Termination Date_Employees",
	   T1."employment_date" as "Employment Date_Employees",
	   T1."salesman_number" as "Salesman Number_Employees",
	   T1."mechanic_number" as "Mechanic Number_Employees",
	   T1."initials" as "Initials_Employees",
	   T1."name" as "Name_Employees"
from "dbo"."employees" T1,
	"LOCOSOFT"."dbo"."employees_worktimes" T5,
	(("dbo"."absence_calendar" T2 left outer join "dbo"."absence_reasons" T3 on T2."reason" = T3."id") left outer join "dbo"."absence_types" T4 on T2."type" = T4."type")
where (T1."employee_number" = T2."employee_number") and (T1."employee_number" = T5."employee_number")
and ((((datepart(weekday, T2."date"))) = T5."dayofweek") and (((T2."date")) <= (getdate())))
) D2
) D1
where ("Validity Date_Worktimes" = c39)
-- order by "Employee Number_Employees" asc