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 outer 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