COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Zeit\Zeit_bezahlte Stunden_2015.imr
TITLE,Zeit_bezahlte Stunden_2015.imr
BEGIN SQL
select T1."No_" as c1,
	   '1' as c2,
	   CASE WHEN ((od_left(T2."Department No_",2)) = '10') THEN ('LBS') WHEN ((od_left(T2."Department No_",2)) = '20') THEN ('WLS') ELSE null END as c3,
	   T1."First Name" as c4,
	   T1."Last Name" as c5,
	   T2."Group No_ 2" as c6,
	   '' as c7,
	   T1."First Name" || ' ' || T1."Last Name" as c8,
	   T2."Leaving Date" as c9,
	   T2."Group No_ 1" as c10,
	   T2."Group No_ 2" as c11,
	   T2."Group No_ 3" as c12,
	   (extract(DAY FROM (now()) - T2."Leaving Date")) as c13,
	   CASE WHEN ((T2."Leaving Date" <= TIMESTAMP '2016-01-31 00:00:00.000') and (T2."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T2."Group No_ 2") END as c14,
	   24 as c15,
	   20 as c16,
	   21 as c17,
	   22 as c18,
	   22 as c19,
	   21 as c20,
	   23 as c21,
	   21 as c22,
	   22 as c23,
	   23 as c24,
	   20 as c25,
	   23 as c26,
	   '2011' as c27,
	   24 * 8.4 as c28,
	   20 * 8.4 as c29,
	   21 * 8.4 as c30,
	   22 * 8.4 as c31,
	   22 * 8.4 as c32,
	   21 * 8.4 as c33,
	   23 * 8.4 as c34,
	   21 * 8.4 as c35,
	   22 * 8.4 as c36,
	   23 * 8.4 as c37,
	   20 * 8.4 as c38,
	   23 * 8.4 as c39,
	   CASE WHEN (T2."Group No_ 3" IN ('KAR','WER')) THEN ('prod. Personal') WHEN (T2."Group No_ 3" IN ('SON')) THEN ('unprod. Personal') ELSE null END as c40,
	   T1."Employment Date" as c41,
	   T3."datum" as c42,
	   T3."Wochentag_neu" as c43,
	   T3."Stunden" as c44,
	   T2."Department No_" as c45,
	   (cast_float(T1."Efficiency %")) as c46,
	   (cast_float(T2."Efficiency %")) as c47,
	   (cast_float(T4."Efficiency %")) as c48,
	   CASE WHEN (T1."Last Name" IN ('Gudek','Löwe 5','L?we 4 Herr Skuballa','Rohrbach','Siladji')) THEN (0) WHEN ((CASE WHEN ((T2."Leaving Date" <= TIMESTAMP '2016-01-31 00:00:00.000') and (T2."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T2."Group No_ 2") END) IN ('AZUBIG')) THEN (((cast_float(T4."Efficiency %"))) / 100) ELSE (((cast_float(T2."Efficiency %"))) / 100) END as c49
from "DMS1"."dbo"."Automag GmbH$Employee_T" T2,
	QSS."C:\GAPS_BMW\Portal\System\IQD\Zeit\current_date_Prognose_für_bez_Std.ims" T3,
	("DMS1"."dbo"."Automag GmbH$Employee" T1 left outer join "DMS1"."dbo"."Automag GmbH$Resource" T4 on T1."No_" = T4."No_")
where (T1."No_" = T2."No_")
and ((((T3."datum" >= T1."Employment Date") and (T3."datum" >= TIMESTAMP '2015-01-01 00:00:00.000')) and ((CASE WHEN (T2."Group No_ 3" IN ('KAR','WER')) THEN ('prod. Personal') WHEN (T2."Group No_ 3" IN ('SON')) THEN ('unprod. Personal') ELSE null END) = 'prod. Personal')) and (not T1."No_" IN ('0045')))
order by c1 asc

END SQL
COLUMN,0,No
COLUMN,1,Hauptbetrieb
COLUMN,2,Standort
COLUMN,3,First Name
COLUMN,4,Last Name
COLUMN,5,Monteur_Gruppe_ori
COLUMN,6,Abteilung
COLUMN,7,Monteur
COLUMN,8,Leaving Date
COLUMN,9,Group No 1
COLUMN,10,Group No 2
COLUMN,11,Group No 3
COLUMN,12,Tage Heute Leaving Date
COLUMN,13,Monteur_Gruppe
COLUMN,14,Jan
COLUMN,15,Feb
COLUMN,16,Mrz
COLUMN,17,Apr
COLUMN,18,Mai
COLUMN,19,Jun
COLUMN,20,Jul
COLUMN,21,Aug
COLUMN,22,Sep
COLUMN,23,Okt
COLUMN,24,Nov
COLUMN,25,Dez
COLUMN,26,Jahr
COLUMN,27,1
COLUMN,28,2
COLUMN,29,3
COLUMN,30,4
COLUMN,31,5
COLUMN,32,6
COLUMN,33,7
COLUMN,34,8
COLUMN,35,9
COLUMN,36,10
COLUMN,37,11
COLUMN,38,12
COLUMN,39,produktiv/unproduktiv
COLUMN,40,Employment Date
COLUMN,41,Datum
COLUMN,42,Wochentag Neu
COLUMN,43,Stunden
COLUMN,44,Department No
COLUMN,45,Efficiency %
COLUMN,46,Efficiency %_Employee_T
COLUMN,47,Efficiency %_Resource
COLUMN,48,Effizienz (Leistungsgrad Soll)