COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\zeiten\zeit_mitarbeiter_ueberstunden_fuer_zeit.imr
TITLE,zeit_mitarbeiter_ueberstunden_fuer_zeit.imr
BEGIN SQL
select T1."No_" as c1,
	   T1."ID No_" as c2,
	   T1."Name" as c3,
	   T1."Last Name" as c4,
	   T1."First Name" as c5,
	   T1."Employment Date" as c6,
	   T1."Leaving Date" as c7,
	   T1."Department No_" as c8,
	   T1."Group No_ 1" as c9,
	   T1."Group No_ 2" as c10,
	   T1."Group No_ 3" as c11,
	   T1."Time Object No_" as c12,
	   T2."Employee No_" as c13,
	   T2."Current Date" as c14,
	   T2."Department No_" as c15,
	   T2."Order No_" as c16,
	   T2."Time Account No_" as c17,
	   (cast_float(T2."Time Account Value")) as c18,
	   '1' as c19,
	   CASE WHEN ((od_left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T1."Department No_",2))) END as c20,
	   T1."Pay Group No_" as c21,
	   (now()) - INTERVAL '001 10:00:00.000' as c22,
	   (extract(DAY FROM (now()) - T1."Leaving Date")) as c23,
	   CASE WHEN ((((extract(DAY FROM (now()) - T1."Leaving Date"))) > 20) and (T1."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('augetretene Mitarbeiter') ELSE (T1."Pay Group No_") END as c24,
	   CASE WHEN (T1."Task Type Group" IN ('MONTEURE','PROD MEIST')) THEN ('prod. Personal') WHEN (T1."Task Type Group" IN ('VERWALTUNG')) THEN ('unprod. Personal') ELSE null END as c25,
	   (cdatetime(T2."Current Date" - cinterval(extract(DAY FROM T2."Current Date") - 1))) as c26,
	   (cdatetime(lastday(cdate(T2."Current Date")))) as c27,
	   CASE WHEN (((now()) - INTERVAL '001 10:00:00.000') BETWEEN ((cdatetime(T2."Current Date" - cinterval(extract(DAY FROM T2."Current Date") - 1)))) AND ((cdatetime(lastday(cdate(T2."Current Date")))))) THEN (T2."Current Date") ELSE null END as c28,
	   '' as c29,
	   CASE WHEN (T1."Last Name" IN ('Schmid','Wittenberg')) THEN (T1."First Name" || ' ' || T1."Last Name" || '_' || '1') ELSE (T1."First Name" || ' ' || T1."Last Name") END as c30,
	   CASE WHEN (T2."Time Account No_" IN ('1101')) THEN (((cast_float(T2."Time Account Value")))) ELSE (0) END as c31,
	   (od_month(T2."Current Date")) as c32,
	   (od_month((now()))) as c33,
	   ((od_month((now())))) - ((od_month(T2."Current Date"))) as c34,
	   (od_year((now()))) as c35,
	   (od_year(T2."Current Date")) as c36,
	   ((od_year((now())))) - ((od_year(T2."Current Date"))) as c37,
	   T1."Client_DB" as c38,
	   CASE WHEN (T1."Client_DB" = '1') THEN ('AHR') WHEN (T1."Client_DB" = '2') THEN ('AAM') ELSE null END as c39,
	   (CASE WHEN ((od_left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T1."Department No_",2))) END) as c40,
	   CASE WHEN (((CASE WHEN ((od_left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T1."Department No_",2))) END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN ((od_left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T1."Department No_",2))) END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN ((od_left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T1."Department No_",2))) END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN ((od_left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T1."Department No_",2))) END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN ((od_left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T1."Department No_",2))) END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN ((od_left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T1."Department No_",2))) END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN ((od_left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T1."Department No_",2))) END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN ((od_left(T1."Department No_",2)) = '90') THEN ('55') ELSE ((od_left(T1."Department No_",2))) END)) IN ('80')) THEN ('WTB') ELSE null END as c41
from "NAVISION"."import"."Employee_T" T1,
	"NAVISION"."import"."Time_Entry_T" T2
where ((T1."No_" = T2."Employee No_") and (T1."Client_DB" = T2."Client_DB"))
and (((((((((T2."Current Date" >= T1."Employment Date") and ((T1."Leaving Date" >= (now())) or (T1."Leaving Date" = TIMESTAMP '1753-01-01 00:00:00.000'))) and (T2."Current Date" >= TIMESTAMP '2021-01-01 00:00:00.000')) and (T2."Time Account No_" IN ('1101','3500','1090'))) and (not T1."No_" IN ('11724','65503','93304'))) and ((CASE WHEN (T1."Task Type Group" IN ('MONTEURE','PROD MEIST')) THEN ('prod. Personal') WHEN (T1."Task Type Group" IN ('VERWALTUNG')) THEN ('unprod. Personal') ELSE null END) = 'prod. Personal')) and ((CASE WHEN (T2."Time Account No_" IN ('1101')) THEN (((cast_float(T2."Time Account Value")))) ELSE (0) END) <> 0)) and (((((od_month((now())))) - ((od_month(T2."Current Date")))) = 1) or ((((od_month((now())))) - ((od_month(T2."Current Date")))) = -11))) and ((((od_year((now())))) - ((od_year(T2."Current Date")))) <= 1))
order by c14 asc,c3 asc,c17 asc

END SQL
COLUMN,0,No
COLUMN,1,Id No
COLUMN,2,Name
COLUMN,3,Last Name
COLUMN,4,First Name
COLUMN,5,Employment Date
COLUMN,6,Leaving Date
COLUMN,7,Department No
COLUMN,8,Group No 1
COLUMN,9,Group No 2
COLUMN,10,Group No 3
COLUMN,11,Time Object No
COLUMN,12,Employee No
COLUMN,13,Datum
COLUMN,14,Department No
COLUMN,15,Order No
COLUMN,16,Time Account No
COLUMN,17,Time Account Value
COLUMN,18,Hauptbetrieb
COLUMN,19,Standort
COLUMN,20,Monteur_Gruppe_ori
COLUMN,21,Heute
COLUMN,22,Tage Heute Leaving Date
COLUMN,23,Monteur_Gruppe
COLUMN,24,produktiv/unproduktiv
COLUMN,25,Monatserster
COLUMN,26,Monatsletzter
COLUMN,27,Datum Tagesbericht
COLUMN,28,Order Number
COLUMN,29,Monteur
COLUMN,30,Überstunden
COLUMN,31,Monat_Datum
COLUMN,32,Monat_Jetzt
COLUMN,33,Diff_Monat
COLUMN,34,Jahr_Jetzt
COLUMN,35,Jahr_Datum
COLUMN,36,Diff_Jahr
COLUMN,37,Hauptbetrieb_ID
COLUMN,38,Hauptbetrieb_Name
COLUMN,39,Standort_ID
COLUMN,40,Standort_Name