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