COGNOS QUERY STRUCTURE,1,1 DATABASE,Locosoft_GC DATASOURCENAME,C:\Gaps_Locosoft_neu\Portal\System\IQD\Zeit\Zeit_Stempelungen_Pausen.imr TITLE,Zeit_Stempelungen_Pausen.imr BEGIN SQL select c1 as c1, c2 as c2, c3 as c3, c4 as c4, c5 as c5, c6 as c6, c7 as c7, c8 as c8, c9 as c9, c10 as c10, c11 as c11, c12 as c12, c13 as c13, c14 as c14, c15 as c15, c16 as c16, c17 as c17, c18 as c18, c19 as c19, c20 as c20, c21 as c21, c22 as c22, c23 as c23, c24 as c24, c25 as c25, c26 as c26, c27 as c27, c28 as c28, c29 as c29, c30 as c30, c31 as c31, c32 as c32, c33 as c33, c34 as c34, c35 as c35, c36 as c36, c37 as c37, c38 as c38, c39 as c39, c40 as c40, c41 as c41, c42 as c42, c43 as c43, c44 as c44, c45 as c45, c46 as c46, c47 as c47, c48 as c48, c49 as c49, c50 as c50, XMAX(c50 for c1) as c51, c52 as c52, XMAX(c52 for c1) as c53, c54 as c54, c55 as c55 from (select c61 as c1, c105 as c2, c104 as c3, c103 as c4, c102 as c5, c101 as c6, c100 as c7, c99 as c8, c98 as c9, c85 as c10, c91 as c11, c97 as c12, c96 as c13, c95 as c14, c94 as c15, c93 as c16, c92 as c17, c91 as c18, '1' as c19, '01' as c20, c90 as c21, c88 as c22, c89 as c23, c88 as c24, c87 as c25, '' as c26, c86 as c27, c85 as c28, c84 as c29, c83 as c30, c82 as c31, c81 as c32, c80 as c33, c79 as c34, c78 as c35, c69 as c36, c77 as c37, c76 as c38, c75 as c39, c74 as c40, c73 as c41, c72 as c42, c71 as c43, c70 as c44, c69 as c45, c68 as c46, c67 as c47, c66 as c48, c65 as c49, c64 as c50, c63 as c52, c62 as c54, c62 as c55, XMAX(c64 for c61) as c56, XMAX(c63 for 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((cast_numberToString(cast_integer(T1."employee_number"))))) || ' - ' || T1."name" as c90, T2."start_time" as c91, T3."description" as c92, T3."type" as c93, T2."duration_minutes" as c94, T2."end_time" as c95, T2."order_positions" as c96, T2."type" as c97, T2."employee_number" as c98, T1."leave_date" as c99, T1."termination_date" as c100, T1."employment_date" as c101, T1."salesman_number" as c102, T1."mechanic_number" as c103, T1."initials" as c104, T1."name" as c105 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 (((((T1."mechanic_number" IS NOT NULL) and (T2."type" = 1)) and (((dayofweek(T2."start_time"))) = T4."dayofweek")) and (((dayofweek(T2."start_time"))) = T5."dayofweek")) and (T2."start_time" >= TIMESTAMP '2017-01-01 00:00:00.000')) ) D2 ) D1 where ((c50 = c56) and (c52 = c57)) order by c1 asc,c11 asc,c10 asc END SQL COLUMN,0,Employee Number_Employees COLUMN,1,Name_Employees COLUMN,2,Initials_Employees COLUMN,3,Mechanic Number_Employees COLUMN,4,Salesman Number_Employees COLUMN,5,Employment Date_Employees COLUMN,6,Termination Date_Employees COLUMN,7,Leave Date_Employees COLUMN,8,Employee Number_Times COLUMN,9,Order Number_Times COLUMN,10,Start Time_Times COLUMN,11,Type_Times COLUMN,12,Order Positions_Times COLUMN,13,End Time_Times COLUMN,14,Duration Minutes_Times COLUMN,15,Type_Time_Types COLUMN,16,Description_Time_Types COLUMN,17,Datum COLUMN,18,Hauptbetrieb COLUMN,19,Standort COLUMN,20,Monteur COLUMN,21,Anwesenheit_ COLUMN,22,produktiv_ COLUMN,23,unproduktiv_ COLUMN,24,Zeitkategorie COLUMN,25,Zeitkategorie2 COLUMN,26,Ges. Std._ COLUMN,27,Order Number COLUMN,28,Wochentag_Datum COLUMN,29,Is Flextime_Employees COLUMN,30,Break Time Registration_Employees COLUMN,31,Is Latest Record_break_times COLUMN,32,Dayofweek_break_times COLUMN,33,Break Start_break_times COLUMN,34,Break End_break_times COLUMN,35,Pause COLUMN,36,Start_Times_Stunde COLUMN,37,Start_Times_Minute COLUMN,38,End_Times_Stunde COLUMN,39,End_Times_Minute COLUMN,40,Start_Times_Minute_Dezimal COLUMN,41,Start_Times_Dezimal COLUMN,42,End_Times_Minute_Dezimal COLUMN,43,End_Times_Dezimal COLUMN,44,Pausenzeit_Stunde COLUMN,45,Dayofweek_Worktimes COLUMN,46,Work Duration_Worktimes COLUMN,47,Worktime Start_Worktimes COLUMN,48,Worktime End_Worktimes COLUMN,49,Validity Date_Worktimes COLUMN,50,Max_Validity_Date COLUMN,51,Validity Date_Break_times COLUMN,52,Max_validity_date_Break_times COLUMN,53,Pausenzeit_Anw_mehr_als_6 COLUMN,54,Anwesenheit