C11/System/ARIntelligence/IQD/Zeit/Zeit_bezahlte Stunden_2016.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Zeit\Zeit_bezahlte Stunden_2016.imr
TITLE,Zeit_bezahlte Stunden_2016
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,
	   CASE WHEN ((T1."No_" IN ('0382','0378')) and (T3."datum" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T3."datum") END 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,
	   CASE WHEN ((T2."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000') and (((extract(DAY FROM (now()) - T2."Leaving Date"))) > 0)) THEN (0) ELSE ((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)) END as c50
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 ((((((CASE WHEN ((T1."No_" IN ('0382','0378')) and (T3."datum" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T3."datum") END) >= T1."Employment Date") and ((CASE WHEN ((T1."No_" IN ('0382','0378')) and (T3."datum" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T3."datum") END) >= TIMESTAMP '2016-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','0396'))) and (not (T1."First Name" || ' ' || T1."Last Name") IN ('Sebastian Eggert','Krzysztof Sowada','Jacek Skuballa','Sebastian Bogner','Sasa Savic','Jawed','Mojtaba Jawed','Nikolaos Moisiadis','Daniele Fecondo','Benjamin Hilla Löwe 8','Dawid Mycek ZAK','Dennis Weist ZAK','Tim Bärmann','Daniel Drechsler','Nicolai von der Recke','Julian Suchomel','Matthias Mühlbauer','Marek Podenas Boetronic','Maximilian Stiller','Niclas Stelzner','Michael Adden','Ruggero Bergamo','Abdel Rahim Abu Hantash','Alessandro Casula','Cemre Sen','Jaroslaw Mittelstaedt','Mahmut Tekin','Miroslav Mijacevic','Christos Goudinoudis','Werner Handke')))
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)_ori
COLUMN,49,Effizienz (Leistungsgrad Soll)