C11/System/LOCOSOFT/IQD/Zeit/Zeit_Stempelungen_Pausen.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_Times" AS "Employee Number_Times",
	"Order Number_Times" AS "Order Number_Times",
	"Start Time_Times" AS "Start Time_Times",
	"Type_Times" AS "Type_Times",
	"Order Positions_Times" AS "Order Positions_Times",
	"End Time_Times" AS "End Time_Times",
	"Duration Minutes_Times" AS "Duration Minutes_Times",
	"Type_Time_Types" AS "Type_Time_Types",
	"Description_Time_Types" AS "Description_Time_Types",
	"Datum" AS "Datum",
	"Hauptbetrieb" AS "Hauptbetrieb",
	"Standort" AS "Standort",
	"Monteur" AS "Monteur",
	"Anwesenheit_" AS "Anwesenheit_",
	"produktiv_" AS "produktiv_",
	"unproduktiv_" AS "unproduktiv_",
	"Zeitkategorie" AS "Zeitkategorie",
	"Zeitkategorie2" AS "Zeitkategorie2",
	"Ges. Std._" AS "Ges. Std._",
	"Order Number" AS "Order Number",
	"Wochentag_Datum" AS "Wochentag_Datum",
	"Is Flextime_Employees" AS "Is Flextime_Employees",
	"Break Time Registration_Employees" AS "Break Time Registration_Employees",
	"Is Latest Record_break_times" AS "Is Latest Record_break_times",
	"Dayofweek_break_times" AS "Dayofweek_break_times",
	"Break Start_break_times" AS "Break Start_break_times",
	"Break End_break_times" AS "Break End_break_times",
	"Pause" AS "Pause",
	"Start_Times_Stunde" AS "Start_Times_Stunde",
	"Start_Times_Minute" AS "Start_Times_Minute",
	"End_Times_Stunde" AS "End_Times_Stunde",
	"End_Times_Minute" AS "End_Times_Minute",
	"Start_Times_Minute_Dezimal" AS "Start_Times_Minute_Dezimal",
	"Start_Times_Dezimal" AS "Start_Times_Dezimal",
	"End_Times_Minute_Dezimal" AS "End_Times_Minute_Dezimal",
	"End_Times_Dezimal" AS "End_Times_Dezimal",
	"Pausenzeit_Stunde" AS "Pausenzeit_Stunde",
	"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",
	"Validity Date_Worktimes" AS "Validity Date_Worktimes",
	MAX("Validity Date_Worktimes") OVER (PARTITION BY "Employee Number_Employees") AS "Max_Validity_Date",
	"Validity Date_Break_times" AS "Validity Date_Break_times",
	MAX("Validity Date_Break_times") OVER (PARTITION BY "Employee Number_Employees") AS "Max_validity_date_Break_times",
	"Pausenzeit_Anw_mehr_als_6" AS "Pausenzeit_Anw_mehr_als_6",
	"Anwesenheit" AS "Anwesenheit"
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_Times",
		"Order Number_Times",
		"Start Time_Times",
		"Type_Times",
		"Order Positions_Times",
		"End Time_Times",
		"Duration Minutes_Times",
		"Type_Time_Types",
		"Description_Time_Types",
		"Start Time_Times" AS "Datum",
		'1' AS "Hauptbetrieb",
		"Standort",
		"Monteur",
		"Anwesenheit_",
		"produktiv_",
		"Anwesenheit_" AS "unproduktiv_",
		"Zeitkategorie",
		'' AS "Zeitkategorie2",
		"Ges. Std._",
		"Order Number_Times" AS "Order Number",
		"Wochentag_Datum",
		"Is Flextime_Employees",
		"Break Time Registration_Employees",
		"Is Latest Record_break_times",
		"Dayofweek_break_times",
		"Break Start_break_times",
		"Break End_break_times",
		"Pause",
		"Start_Times_Stunde",
		"Start_Times_Minute",
		"End_Times_Stunde",
		"End_Times_Minute",
		"Start_Times_Minute_Dezimal",
		"Start_Times_Dezimal",
		"End_Times_Minute_Dezimal",
		"End_Times_Dezimal",
		"Pause" AS "Pausenzeit_Stunde",
		"Dayofweek_Worktimes",
		"Work Duration_Worktimes",
		"Worktime Start_Worktimes",
		"Worktime End_Worktimes",
		"Validity Date_Worktimes",
		"Validity Date_Break_times",
		"Pausenzeit_Anw_mehr_als_6",
		"Pausenzeit_Anw_mehr_als_6" AS "Anwesenheit",
		MAX("Validity Date_Worktimes") OVER (PARTITION BY "Employee Number_Employees") AS c56,
		MAX("Validity Date_Break_times") OVER (PARTITION BY "Employee Number_Employees") AS c57
	FROM (
		SELECT T1."employee_number" AS "Employee Number_Employees",
			(
				CASE 
					WHEN (
							(
								CASE 
									WHEN (T3."type" = 1)
										THEN (T2."duration_minutes" / 60)
									ELSE (0)
									END
								) >= 6.1
							)
						THEN ((T4."break_end" - T4."break_start") * - 1)
					ELSE (0)
					END
				) AS "Pausenzeit_Anw_mehr_als_6",
			T4."validity_date" AS "Validity Date_Break_times",
			T5."validity_date" AS "Validity Date_Worktimes",
			T5."worktime_end" AS "Worktime End_Worktimes",
			T5."worktime_start" AS "Worktime Start_Worktimes",
			T5."work_duration" AS "Work Duration_Worktimes",
			T5."dayofweek" AS "Dayofweek_Worktimes",
			T4."break_end" - T4."break_start" AS "Pause",
			((od_hour(T2."end_time"))) + (((od_minute(T2."end_time"))) / 60) AS "End_Times_Dezimal",
			((od_minute(T2."end_time"))) / 60 AS "End_Times_Minute_Dezimal",
			((od_hour(T2."start_time"))) + (((od_minute(T2."start_time"))) / 60) AS "Start_Times_Dezimal",
			((od_minute(T2."start_time"))) / 60 AS "Start_Times_Minute_Dezimal",
			(od_minute(T2."end_time")) AS "End_Times_Minute",
			(od_hour(T2."end_time")) AS "End_Times_Stunde",
			(od_minute(T2."start_time")) AS "Start_Times_Minute",
			(od_hour(T2."start_time")) AS "Start_Times_Stunde",
			T4."break_end" AS "Break End_break_times",
			T4."break_start" AS "Break Start_break_times",
			T4."dayofweek" AS "Dayofweek_break_times",
			T4."is_latest_record" AS "Is Latest Record_break_times",
			T1."break_time_registration" AS "Break Time Registration_Employees",
			T1."is_flextime" AS "Is Flextime_Employees",
			(datepart(weekday, T2."start_time")) AS "Wochentag_Datum",
			T2."order_number" AS "Order Number_Times",
			T2."duration_minutes" / 60 AS "Ges. Std._",
			CASE 
				WHEN (T2."type" = 2)
					THEN ('produktiv')
				ELSE ('unproduktiv')
				END AS "Zeitkategorie",
			CASE 
				WHEN (T3."type" = 1)
					THEN (T2."duration_minutes" / 60)
				ELSE (0)
				END AS "Anwesenheit_",
			CASE 
				WHEN (T3."type" = 2)
					THEN (T2."duration_minutes" / 60)
				ELSE (0)
				END AS "produktiv_",
			(rtrim((((T1."employee_number"))))) + ' - ' + T1."name" AS "Monteur",
			'0' + (rtrim((((T1."subsidiary"))))) AS "Standort",
			T2."start_time" AS "Start Time_Times",
			T3."description" AS "Description_Time_Types",
			T3."type" AS "Type_Time_Types",
			T2."duration_minutes" AS "Duration Minutes_Times",
			T2."end_time" AS "End Time_Times",
			T2."order_positions" AS "Order Positions_Times",
			T2."type" AS "Type_Times",
			T2."employee_number" AS "Employee Number_Times",
			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_breaktimes" T4,
			"LOCOSOFT"."dbo"."employees_worktimes" T5,
			(
				"dbo"."times" T2 LEFT JOIN "dbo"."time_types" T3 ON T2."type" = T3."type"
				)
		WHERE (T1."employee_number" = T2."employee_number")
			AND (T1."employee_number" = T4."employee_number")
			AND (T1."employee_number" = T5."employee_number")
			AND (
				(
					(
						(T2."type" = 1)
						AND (((datepart(weekday, T2."start_time"))) = T4."dayofweek")
						)
					AND (((datepart(weekday, T2."start_time"))) = T5."dayofweek")
					)
				AND (T2."start_time" >= convert(DATETIME, '2017-01-01 00:00:00.000'))
				)
		) D2
	) D1
WHERE (
		("Validity Date_Worktimes" = c56)
		AND ("Validity Date_Break_times" = c57)
		)
	-- order by "Employee Number_Employees" asc,"Start Time_Times" asc,"Order Number_Times" asc