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 c69 as c1,
	   c70 as c2,
	   c125 as c3,
	   c72 as c4,
	   c124 as c5,
	   c123 as c6,
	   c122 as c7,
	   c121 as c8,
	   c120 as c9,
	   c119 as c10,
	   c118 as c11,
	   c117 as c12,
	   c116 as c13,
	   c115 as c14,
	   c114 as c15,
	   c113 as c16,
	   c112 as c17,
	   c111 as c18,
	   c71 as c19,
	   c110 as c20,
	   c109 as c21,
	   c108 as c22,
	   c107 as c23,
	   c106 as c24,
	   0 as c25,
	   c105 as c26,
	   c104 as c27,
	   c103 as c28,
	   c102 as c29,
	   '1' as c30,
	   c79 as c31,
	   c101 as c32,
	   c100 as c33,
	   c99 as c34,
	   '' as c35,
	   '' as c36,
	   c98 as c37,
	   c97 as c38,
	   c96 as c39,
	   c95 as c40,
	   c94 as c41,
	   c93 as c42,
	   c92 as c43,
	   c91 as c44,
	   c90 as c45,
	   c89 as c46,
	   c88 as c47,
	   c87 as c48,
	   c86 as c49,
	   c85 as c50,
	   c84 as c51,
	   c78 as c52,
	   c83 as c53,
	   c82 as c54,
	   c81 as c55,
	   c80 as c56,
	   c79 as c57,
	   c78 as c58,
	   c77 as c59,
	   c76 as c60,
	   c75 as c61,
	   c74 as c62,
	   c73 as c63,
	   XSUM(c76 for c70,c69) as c64,
	   CASE WHEN ((c72 IN ('670','671','672','675','676')) and ((XSUM(c76 for c70,c69)) = 0)) THEN (0) ELSE ((c71)) END as c65
from 
(select T1."Employee No_" as c69,
	   (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 c70,
	   (cast_float(T1."Time Account Value")) as c71,
	   T1."Time Account No_" as c72,
	   (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 c73,
	   T4."Home Page 2" as c74,
	   CASE WHEN (T1."Time Account No_" BETWEEN '299' AND '450') THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c75,
	   CASE WHEN (T1."Time Account No_" = '1200') THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c76,
	   T4."Task Type Group" as c77,
	   T3."Department No_" as c78,
	   (od_left(T3."Department No_",2)) as c79,
	   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 c80,
	   (rtrim((cast_numberToString(cast_integer(T2."No_"))))) || ' - ' || T2."Description" as c81,
	   CASE WHEN (T2."No_" IN (300,301)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c82,
	   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 c83,
	   T4."Employment Date" as c84,
	   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 c85,
	   (now()) - INTERVAL '001 10:00:00.000' as c86,
	   (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 c87,
	   (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 c88,
	   CASE WHEN (T4."Function Code" IN ('AZUBI','MECH','SERVHILF','SERVTECH','WAGENPFLEG','WERKMEI')) THEN ('prod. Personal') ELSE ('unprod. Personal') END as c89,
	   ((CASE WHEN (T4."Function Code" IN ('AZUBI','MECH','SERVHILF','SERVTECH','WAGENPFLEG','WERKMEI')) THEN ('prod. Personal') ELSE ('unprod. Personal') END)) as c90,
	   (extract(DAY FROM (now()) - T3."Leaving Date")) as c91,
	   T3."Group No_ 3" as c92,
	   T3."Group No_ 2" as c93,
	   T3."Group No_ 1" as c94,
	   T3."Leaving Date" as c95,
	   CASE WHEN (T2."No_" IN (200)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c96,
	   CASE WHEN (T2."No_" IN (378)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c97,
	   T4."First Name" || ' ' || T4."Last Name" as c98,
	   T4."Function Code" as c99,
	   T4."Last Name" as c100,
	   T4."First Name" as c101,
	   (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 c102,
	   CASE WHEN (T2."No_" IN (350,351)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c103,
	   CASE WHEN (T2."No_" = 355) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c104,
	   CASE WHEN (T2."No_" = 450) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c105,
	   CASE WHEN (T2."No_" = 354) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c106,
	   CASE WHEN (T2."No_" = 352) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c107,
	   CASE WHEN (T2."No_" = 379) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c108,
	   CASE WHEN (T2."No_" IN (370,371,372)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c109,
	   CASE WHEN (T2."No_" IN (100)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c110,
	   T2."Description 2" as c111,
	   T2."Description" as c112,
	   T2."No_" as c113,
	   T1."Record protected" as c114,
	   T1."Generating Function" as c115,
	   T1."TA Formatting" as c116,
	   T1."TA Class (Vacation Reduction)" as c117,
	   T1."TA Class (Employee Info)" as c118,
	   T1."TA Class (Individual)" as c119,
	   T1."TA Class (Time Processing)" as c120,
	   T1."TA Class (Absent Days)" as c121,
	   T1."TA Class (Statistics)" as c122,
	   T1."TA Class (General)" as c123,
	   T1."Time Account Value" as c124,
	   T1."Department No_" as c125
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 c70 asc,c69 asc
) D1

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_ori
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
COLUMN,63,Summe Sollzeit
COLUMN,64,Zeitdauer