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 c61 as "Employee Number_Employees", c106 as "Name_Employees", c105 as "Initials_Employees", c104 as "Mechanic Number_Employees", c103 as "Salesman Number_Employees", c102 as "Employment Date_Employees", c101 as "Termination Date_Employees", c100 as "Leave Date_Employees", c99 as "Employee Number_Times", c85 as "Order Number_Times", c92 as "Start Time_Times", c98 as "Type_Times", c97 as "Order Positions_Times", c96 as "End Time_Times", c95 as "Duration Minutes_Times", c94 as "Type_Time_Types", c93 as "Description_Time_Types", c92 as "Datum", '1' as "Hauptbetrieb", c91 as "Standort", c90 as "Monteur", c88 as "Anwesenheit_", c89 as "produktiv_", c88 as "unproduktiv_", c87 as "Zeitkategorie", '' as "Zeitkategorie2", c86 as "Ges. Std._", c85 as "Order Number", c84 as "Wochentag_Datum", c83 as "Is Flextime_Employees", c82 as "Break Time Registration_Employees", c81 as "Is Latest Record_break_times", c80 as "Dayofweek_break_times", c79 as "Break Start_break_times", c78 as "Break End_break_times", c69 as "Pause", c77 as "Start_Times_Stunde", c76 as "Start_Times_Minute", c75 as "End_Times_Stunde", c74 as "End_Times_Minute", c73 as "Start_Times_Minute_Dezimal", c72 as "Start_Times_Dezimal", c71 as "End_Times_Minute_Dezimal", c70 as "End_Times_Dezimal", c69 as "Pausenzeit_Stunde", c68 as "Dayofweek_Worktimes", c67 as "Work Duration_Worktimes", c66 as "Worktime Start_Worktimes", c65 as "Worktime End_Worktimes", c64 as "Validity Date_Worktimes", c63 as "Validity Date_Break_times", c62 as "Pausenzeit_Anw_mehr_als_6", c62 as "Anwesenheit", MAX(c64) OVER (partition by c61) as c56, MAX(c63) OVER (partition by c61) as c57 from (select T1."employee_number" as c61, (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 c62, T4."validity_date" as c63, T5."validity_date" as c64, T5."worktime_end" as c65, T5."worktime_start" as c66, T5."work_duration" as c67, T5."dayofweek" as c68, T4."break_end" - T4."break_start" as c69, ((od_hour(T2."end_time"))) + (((od_minute(T2."end_time"))) / 60) as c70, ((od_minute(T2."end_time"))) / 60 as c71, ((od_hour(T2."start_time"))) + (((od_minute(T2."start_time"))) / 60) as c72, ((od_minute(T2."start_time"))) / 60 as c73, (od_minute(T2."end_time")) as c74, (od_hour(T2."end_time")) as c75, (od_minute(T2."start_time")) as c76, (od_hour(T2."start_time")) as c77, T4."break_end" as c78, T4."break_start" as c79, T4."dayofweek" as c80, T4."is_latest_record" as c81, T1."break_time_registration" as c82, T1."is_flextime" as c83, (dayofweek(T2."start_time")) as c84, T2."order_number" as c85, T2."duration_minutes" / 60 as c86, CASE WHEN (T2."type" = 2) THEN ('produktiv') ELSE ('unproduktiv') END as c87, CASE WHEN (T3."type" = 1) THEN (T2."duration_minutes" / 60) ELSE (0) END as c88, CASE WHEN (T3."type" = 2) THEN (T2."duration_minutes" / 60) ELSE (0) END as c89, (rtrim(((convert(varchar(50), T1."employee_number"))))) + ' - ' + T1."name" as c90, '0' + (rtrim(((convert(varchar(50), T1."subsidiary"))))) as c91, T2."start_time" as c92, T3."description" as c93, T3."type" as c94, T2."duration_minutes" as c95, T2."end_time" as c96, T2."order_positions" as c97, T2."type" as c98, T2."employee_number" as c99, T1."leave_date" as c100, T1."termination_date" as c101, T1."employment_date" as c102, T1."salesman_number" as c103, T1."mechanic_number" as c104, T1."initials" as c105, T1."name" as c106 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 (((dayofweek(T2."start_time"))) = T4."dayofweek")) and (((dayofweek(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