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 c42 as "Employee Number_Employees",
	   c72 as "Name_Employees",
	   c71 as "Initials_Employees",
	   c70 as "Mechanic Number_Employees",
	   c69 as "Salesman Number_Employees",
	   c68 as "Employment Date_Employees",
	   c67 as "Termination Date_Employees",
	   c66 as "Leave Date_Employees",
	   c65 as "Employee Number_Absence_Cal",
	   c49 as "Date_Absence_Cal",
	   c64 as "Type_Absence_Cal",
	   c63 as "Is Payed_Absence_Cal",
	   c62 as "Day Contingent_Absence_Cal",
	   c61 as "Reason_Absence_Cal",
	   c60 as "Booking Flag_Absence_Cal",
	   c59 as "Id_Absence_Reas",
	   c58 as "Description_Absence_Reas",
	   c57 as "Is Annual Vacation_Absence_Reas",
	   c56 as "Type_Absence_Types",
	   c55 as "Description_Absence_Types",
	   c54 as "Hauptbetrieb",
	   c53 as "Standort",
	   c52 as "Monteur",
	   8 as "Dummy_Arbeitszeit_Tag",
	   c50 as "Abwesenheit",
	   'Abwesenheit' as "Zeitkategorie",
	   c51 as "Zeitkategorie2",
	   c50 as "Ges. Std.",
	   c49 as "Datum",
	   '' as "Order Number",
	   c48 as "Wochentag_Datum",
	   c47 as "Validity Date_Worktimes",
	   c46 as "Dayofweek_Worktimes",
	   c43 as "Work Duration_Worktimes",
	   c45 as "Worktime Start_Worktimes",
	   c44 as "Worktime End_Worktimes",
	   c43 as "Arbeitszeit_Tag",
	   MAX(c47) OVER (partition by c42) as c39
from 
(select T1."employee_number" as c42,
	   T5."work_duration" as c43,
	   T5."worktime_end" as c44,
	   T5."worktime_start" as c45,
	   T5."dayofweek" as c46,
	   T5."validity_date" as c47,
	   (datepart({weekday},T2."date")) as c48,
	   T2."date" as c49,
	   (T2."day_contingent" * T5."work_duration") as c50,
	   T3."id" + ' - ' + T3."description" as c51,
	   (rtrim((convert(varchar(50), T1."employee_number")))) + ' - ' + T1."name" as c52,
	   '0' + (rtrim((convert(varchar(50), T1."subsidiary")))) as c53,
	   CASE WHEN (('0' + (rtrim((convert(varchar(50), T1."subsidiary"))))) IN ('015')) THEN ('15') ELSE ('1') END as c54,
	   T4."description" as c55,
	   T4."type" as c56,
	   T3."is_annual_vacation" as c57,
	   T3."description" as c58,
	   T3."id" as c59,
	   T2."booking_flag" as c60,
	   T2."reason" as c61,
	   T2."day_contingent" as c62,
	   T2."is_payed" as c63,
	   T2."type" as c64,
	   T2."employee_number" as c65,
	   T1."leave_date" as c66,
	   T1."termination_date" as c67,
	   T1."employment_date" as c68,
	   T1."salesman_number" as c69,
	   T1."mechanic_number" as c70,
	   T1."initials" as c71,
	   T1."name" as c72
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 ((cdate(T2."date")) <= (@CURRENT_DATE)))
) D2
) D1
where ("Validity Date_Worktimes" = c39)
-- order by "Employee Number_Employees" asc