C11/System/LOCOSOFT/IQD/Zeit/Zeit_Stempelungen_Pausen.iqd

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