C11/System/ARI/IQD/Zeit/Zeit_2_Ryma.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,ARIntelligence
DATASOURCENAME,C:\GlobalCube\System\ARI\IQD\Zeit\Zeit_2_Ryma.imr
TITLE,Zeit_2_Ryma.imr
BEGIN SQL
select T1."Employee No_" as c1,
	   (cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END)) as c2,
	   T1."Department No_" as c3,
	   T1."Time Account No_" as c4,
	   T1."Time Account Value" as c5,
	   T1."TA Class (General)" as c6,
	   T1."TA Class (Statistics)" as c7,
	   T1."TA Class (Absent Days)" as c8,
	   T1."TA Class (Time Processing)" as c9,
	   T1."TA Class (Individual)" as c10,
	   T1."TA Class (Employee Info)" as c11,
	   T1."TA Class (Vacation Reduction)" as c12,
	   T1."TA Formatting" as c13,
	   T1."Generating Function" as c14,
	   T1."Record protected" as c15,
	   T2."No_" as c16,
	   T2."Description" as c17,
	   T2."Description 2" as c18,
	   (cast_float(T1."Time Account Value")) as c19,
	   CASE WHEN (T2."No_" IN (100)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c20,
	   CASE WHEN (T2."No_" IN (370,371,372)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c21,
	   CASE WHEN (T2."No_" = 379) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c22,
	   CASE WHEN (T2."No_" = 352) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c23,
	   CASE WHEN (T2."No_" = 354) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c24,
	   0 as c25,
	   CASE WHEN (T2."No_" = 450) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c26,
	   CASE WHEN (T2."No_" = 355) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c27,
	   CASE WHEN (T2."No_" IN (350,351)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c28,
	   (CASE WHEN (T2."No_" IN (370,371,372)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."No_" = 379) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."No_" = 352) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."No_" = 354) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) + 0 + (CASE WHEN (T2."No_" = 450) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."No_" = 355) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."No_" IN (350,351)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."No_" IN (300,301)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) as c29,
	   '1' as c30,
	   (od_left(T3."Department No_",2)) as c31,
	   T4."First Name" as c32,
	   T4."Last Name" as c33,
	   T4."Function Code" as c34,
	   '' as c35,
	   '' as c36,
	   T4."First Name" || ' ' || T4."Last Name" as c37,
	   CASE WHEN (T2."No_" IN (378)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c38,
	   CASE WHEN (T2."No_" IN (200)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c39,
	   T3."Leaving Date" as c40,
	   T3."Group No_ 1" as c41,
	   T3."Group No_ 2" as c42,
	   T3."Group No_ 3" as c43,
	   (extract(DAY FROM (now()) - T3."Leaving Date")) as c44,
	   ((CASE WHEN (T4."Function Code" IN ('AZUBI','MECH','SERVHILF','SERVTECH','WAGENPFLEG','WERKMEI')) THEN ('prod. Personal') ELSE ('unprod. Personal') END)) as c45,
	   CASE WHEN (T4."Function Code" IN ('AZUBI','MECH','SERVHILF','SERVTECH','WAGENPFLEG','WERKMEI')) THEN ('prod. Personal') ELSE ('unprod. Personal') END as c46,
	   (cdatetime(((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END))) - cinterval(extract(DAY FROM ((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END)))) - 1))) as c47,
	   (cdatetime(lastday(cdate(((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END))))))) as c48,
	   (now()) - INTERVAL '001 10:00:00.000' as c49,
	   CASE WHEN (((now()) - INTERVAL '001 10:00:00.000') BETWEEN ((cdatetime(((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END))) - cinterval(extract(DAY FROM ((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END)))) - 1)))) AND ((cdatetime(lastday(cdate(((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END))))))))) THEN (((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END)))) ELSE null END as c50,
	   T4."Employment Date" as c51,
	   T3."Department No_" as c52,
	   CASE WHEN ((extract(DAY FROM (now()) - ((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END))))) <= 93) THEN (((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END)))) ELSE null END as c53,
	   CASE WHEN (T2."No_" IN (300,301)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c54,
	   (rtrim((cast_numberToString(cast_integer(T2."No_"))))) || ' - ' || T2."Description" as c55,
	   CASE WHEN ((T3."Leaving Date" < (now())) and (T3."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T4."Function Code") END as c56,
	   (od_left(T3."Department No_",2)) as c57,
	   T3."Department No_" as c58,
	   T4."Task Type Group" as c59,
	   CASE WHEN (T1."Time Account No_" = '1200') THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c60,
	   CASE WHEN (T1."Time Account No_" BETWEEN '299' AND '450') THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c61,
	   T4."Home Page 2" as c62,
	   (asciiz(extract(YEAR FROM T4."Employment Date"),4) || '-' || asciiz(extract(MONTH FROM T4."Employment Date"),2) || '-' || asciiz(extract(DAY FROM T4."Employment Date"),2)) as c63
from "Gottstein7x"."dbo"."AH Gottstein$Employee_T" T3,
	(("Gottstein7x"."dbo"."AH Gottstein$Time Entry_T" T1 left outer join "Gottstein7x"."dbo"."AH Gottstein$Time Account_T" T2 on T1."Time Account No_" = (cast_numberToString(cast_integer(T2."No_")))) left outer join "Gottstein7x"."dbo"."AH Gottstein$Employee" T4 on T1."Employee No_" = T4."No_")
where (T4."No_" = T3."No_")
and (((((((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END))) >= T4."Employment Date") and (((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END))) >= DATE '2022-01-01')) and (((cdate(CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Current Date" <= TIMESTAMP '2019-02-22 00:00:00.000')) THEN (DATE '1900-01-01') ELSE (T1."Current Date") END))) <= (@CURRENT_DATE))) and ((CASE WHEN (T4."Function Code" IN ('AZUBI','MECH','SERVHILF','SERVTECH','WAGENPFLEG','WERKMEI')) THEN ('prod. Personal') ELSE ('unprod. Personal') END) IN ('prod. Personal','unprod. Personal'))) and ((((T1."Time Account No_" BETWEEN '299' AND '450') or (T1."Time Account No_" = '1200')) or (T1."Time Account No_" = '1410')) or (T1."Time Account No_" IN ('670','671','672','675','676'))))
order by c2 asc,c1 asc

END SQL
COLUMN,0,Employee No
COLUMN,1,Datum
COLUMN,2,Department No_ori
COLUMN,3,Time Account No
COLUMN,4,Time Account Value
COLUMN,5,Ta Class (general)
COLUMN,6,Ta Class (statistics)
COLUMN,7,Ta Class (absent Days)
COLUMN,8,Ta Class (time Processing)
COLUMN,9,Ta Class (individual)
COLUMN,10,Ta Class (employee Info)
COLUMN,11,Ta Class (vacation Reduction)
COLUMN,12,Ta Formatting
COLUMN,13,Generating Function
COLUMN,14,Record Protected
COLUMN,15,No
COLUMN,16,Description
COLUMN,17,Description 2
COLUMN,18,Zeitdauer
COLUMN,19,gesamt Stunden
COLUMN,20,krank
COLUMN,21,Wehr- /Zivildienst
COLUMN,22,Sonderurlaub
COLUMN,23,Berufsschule
COLUMN,24,Innung Azubi
COLUMN,25,Feiertag
COLUMN,26,Schulung extern
COLUMN,27,Urlaub
COLUMN,28,W-fix Stunden
COLUMN,29,Hauptbetrieb_ID
COLUMN,30,Standort
COLUMN,31,First Name
COLUMN,32,Last Name
COLUMN,33,Monteur_Gruppe_ori
COLUMN,34,Abteilung
COLUMN,35,Order Number
COLUMN,36,Monteur
COLUMN,37,Zeitausgleich
COLUMN,38,Überstunden
COLUMN,39,Leaving Date
COLUMN,40,Group No 1
COLUMN,41,Group No 2
COLUMN,42,Group No 3
COLUMN,43,Tage Heute Leaving Date
COLUMN,44,Monteur_Gruppe
COLUMN,45,produktiv/unproduktiv
COLUMN,46,Monatserster
COLUMN,47,Monatsletzter
COLUMN,48,Heute
COLUMN,49,Datum Tagesbericht
COLUMN,50,Employment Date
COLUMN,51,Department No
COLUMN,52,Datum_Monteurlisten
COLUMN,53,Fehlzeiten genehm./ungenem.
COLUMN,54,Activity Desc
COLUMN,55,Monteur_Gruppe_2
COLUMN,56,Standort_ID
COLUMN,57,Department No
COLUMN,58,Task Type Group
COLUMN,59,Sollzeit
COLUMN,60,abwesend
COLUMN,61,Home Page 2
COLUMN,62,Employment Date