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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Locosoft_GC
DATASOURCENAME,C:\Gaps_Locosoft_neu\Portal\System\IQD\Zeit\Zeit_Abwesenheit_neu.imr
TITLE,Zeit_Abwesenheit_neu.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,
	   XMAX(c32 for c1) as c37,
	   c38 as c38
from 
(select c42 as c1,
	   c70 as c2,
	   c69 as c3,
	   c68 as c4,
	   c67 as c5,
	   c66 as c6,
	   c65 as c7,
	   c64 as c8,
	   c63 as c9,
	   c49 as c10,
	   c62 as c11,
	   c61 as c12,
	   c60 as c13,
	   c59 as c14,
	   c58 as c15,
	   c57 as c16,
	   c56 as c17,
	   c55 as c18,
	   c54 as c19,
	   c53 as c20,
	   '1' as c21,
	   '01' as c22,
	   c52 as c23,
	   8 as c24,
	   c50 as c25,
	   'Abwesenheit' as c26,
	   c51 as c27,
	   c50 as c28,
	   c49 as c29,
	   '' as c30,
	   c48 as c31,
	   c47 as c32,
	   c46 as c33,
	   c43 as c34,
	   c45 as c35,
	   c44 as c36,
	   c43 as c38,
	   XMAX(c47 for c42) as c39
from 
(select T1."employee_number" as c42,
	   T5."work_duration" as c43,
	   T5."worktime_end" as c44,
	   T5."worktime_start" as c45,
	   T5."dayofweek" as c46,
	   T5."validity_date" as c47,
	   (dayofweek(T2."date")) as c48,
	   T2."date" as c49,
	   (T2."day_contingent" * T5."work_duration") as c50,
	   T3."id" || ' - ' || T3."description" as c51,
	   (rtrim((cast_numberToString(cast_integer(T1."employee_number"))))) || ' - ' || T1."name" as c52,
	   T4."description" as c53,
	   T4."type" as c54,
	   T3."is_annual_vacation" as c55,
	   T3."description" as c56,
	   T3."id" as c57,
	   T2."booking_flag" as c58,
	   T2."reason" as c59,
	   T2."day_contingent" as c60,
	   T2."is_payed" as c61,
	   T2."type" as c62,
	   T2."employee_number" as c63,
	   T1."leave_date" as c64,
	   T1."termination_date" as c65,
	   T1."employment_date" as c66,
	   T1."salesman_number" as c67,
	   T1."mechanic_number" as c68,
	   T1."initials" as c69,
	   T1."name" as c70
from "dbo"."employees" T1,
	"LOCOSOFT"."dbo"."employees_worktimes" T5,
	(("dbo"."absence_calendar" T2 left outer join "dbo"."absence_reasons" T3 on T2."reason" = T3."id") left outer join "dbo"."absence_types" T4 on T2."type" = T4."type")
where (T1."employee_number" = T2."employee_number") and (T1."employee_number" = T5."employee_number")
and (((T1."mechanic_number" IS NOT NULL) and (((dayofweek(T2."date"))) = T5."dayofweek")) and (T2."reason" <> 'sns'))
) D2
) D1
where (c32 = c39)
order by c1 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_Absence_Cal
COLUMN,9,Date_Absence_Cal
COLUMN,10,Type_Absence_Cal
COLUMN,11,Is Payed_Absence_Cal
COLUMN,12,Day Contingent_Absence_Cal
COLUMN,13,Reason_Absence_Cal
COLUMN,14,Booking Flag_Absence_Cal
COLUMN,15,Id_Absence_Reas
COLUMN,16,Description_Absence_Reas
COLUMN,17,Is Annual Vacation_Absence_Reas
COLUMN,18,Type_Absence_Types
COLUMN,19,Description_Absence_Types
COLUMN,20,Hauptbetrieb
COLUMN,21,Standort
COLUMN,22,Monteur
COLUMN,23,Dummy_Arbeitszeit_Tag
COLUMN,24,Abwesenheit
COLUMN,25,Zeitkategorie
COLUMN,26,Zeitkategorie2
COLUMN,27,Ges. Std.
COLUMN,28,Datum
COLUMN,29,Order Number
COLUMN,30,Wochentag_Datum
COLUMN,31,Validity Date_Worktimes
COLUMN,32,Dayofweek_Worktimes
COLUMN,33,Work Duration_Worktimes
COLUMN,34,Worktime Start_Worktimes
COLUMN,35,Worktime End_Worktimes
COLUMN,36,Max_Validity_Date_Worktimes
COLUMN,37,Arbeitszeit_Tag