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 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,
	   ((datepart({hour},T2."end_time"))) + (((datepart({minute},T2."end_time"))) / 60) as c70,
	   ((datepart({minute},T2."end_time"))) / 60 as c71,
	   ((datepart({hour},T2."start_time"))) + (((datepart({minute},T2."start_time"))) / 60) as c72,
	   ((datepart({minute},T2."start_time"))) / 60 as c73,
	   (datepart({minute},T2."end_time")) as c74,
	   (datepart({hour},T2."end_time")) as c75,
	   (datepart({minute},T2."start_time")) as c76,
	   (datepart({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,
	   (datepart({weekday},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 (((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