COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\GAPS_BMW\Portal\System\IQD\Zeit\Zeit_bezahlte Stunden.imr
TITLE,Zeit_bezahlte Stunden.imr
BEGIN SQL
select T1."No_" as c1,
	   '1' as c2,
	   'LBS' 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 ((((extract(DAY FROM (now()) - T2."Leaving Date"))) > 0) and (T2."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T2."Group No_ 2") END as c14,
	   21 as c15,
	   20 as c16,
	   23 as c17,
	   21 as c18,
	   22 as c19,
	   22 as c20,
	   21 as c21,
	   23 as c22,
	   21 as c23,
	   21 as c24,
	   22 as c25,
	   22 as c26,
	   '2011' as c27,
	   21 * 0 as c28,
	   20 * 8.4 as c29,
	   23 * 8.4 as c30,
	   21 * 8.4 as c31,
	   22 * 8.4 as c32,
	   22 * 8.4 as c33,
	   21 * 8.4 as c34,
	   23 * 8.4 as c35,
	   21 * 8.4 as c36,
	   21 * 8.4 as c37,
	   22 * 8.4 as c38,
	   22 * 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
from "DMS1"."dbo"."Automag GmbH$Employee" T1,
	"DMS1"."dbo"."Automag GmbH$Employee_T" T2,
	QSS."C:\GAPS_BMW\Portal\System\IQD\Zeit\current_date_Prognose_für_bez_Std.ims" T3
where (T1."No_" = T2."No_")
and ((T3."datum" >= T1."Employment Date") and (T3."datum" <= TIMESTAMP '2011-12-31 00:00:00.000'))
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