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",
			(((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 JOIN "dbo"."absence_reasons" T3 ON T2."reason" = T3."id"
					) LEFT 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