C11/System/CARLO/IQD/Zeit/zeit_2_c11.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_CARLO
DATASOURCENAME,C:\GlobalCube\System\CARLO\IQD\zeit\zeit_2_c11.imr
TITLE,zeit_2_c11.imr
BEGIN SQL
select c1 as c1,
	   c2 as c2,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   c6 as c6,
	   c7 as c7,
	   c8 as c8,
	   c9 as c9,
	   RSUM(c5) as c10,
	   c11 as c11,
	   RSUM(c11 for c2,c1) as c12,
	   RSUM(c11) as c13,
	   RSUM(c11) as c14,
	   c15 as c15,
	   c16 as c16,
	   c17 as c17,
	   c18 as c18,
	   c19 as c19,
	   c20 as c20,
	   c21 as c21,
	   c22 as c22,
	   c23 as c23,
	   c24 as c24,
	   c25 as c25,
	   c26 as c26,
	   c27 as c27,
	   c28 as c28,
	   c29 as c29,
	   c30 as c30,
	   c31 as c31,
	   c32 as c32,
	   c33 as c33,
	   c34 as c34,
	   c35 as c35,
	   c36 as c36,
	   c37 as c37,
	   c38 as c38,
	   c39 as c39,
	   c40 as c40,
	   c41 as c41,
	   c42 as c42,
	   c43 as c43,
	   c44 as c44,
	   c45 as c45,
	   c46 as c46,
	   c47 as c47,
	   c48 as c48,
	   c49 as c49,
	   c50 as c50,
	   c51 as c51,
	   c52 as c52,
	   c53 as c53,
	   c54 as c54,
	   c55 as c55,
	   c56 as c56,
	   c57 as c57,
	   c58 as c58,
	   c59 as c59,
	   c60 as c60
from 
(select T1."Employee No_" as c1,
	   T1."Current Date" as c2,
	   T1."Department No_" as c3,
	   T1."Time Account No_" as c4,
	   T1."Time Account Value" as c5,
	   T2."No_" as c6,
	   T2."Description" as c7,
	   T2."Description 2" as c8,
	   T1."Time Account Value" as c9,
	   CASE WHEN (T2."No_" IN (100)) THEN (T1."Time Account Value") ELSE (0) END as c11,
	   '1' as c15,
	   CASE WHEN ((CASE WHEN ((((extract(DAY FROM (now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END) IN ('MONTEUR1','AZUBI 4','AZUBI 3','SPENG FL','AZUBI 1','AZUBI 2')) THEN ('00') WHEN ((CASE WHEN ((((extract(DAY FROM (now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END) = 'MONTEUR2') THEN ('02') WHEN ((T4."Filialcode" = '') and (not (CASE WHEN ((((extract(DAY FROM (now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END) IN ('MONTEUR1','AZUBI 4','AZUBI 3','SPENG FL','AZUBI 1','AZUBI 2','MONTEUR2'))) THEN ('00') ELSE (T4."Filialcode") END as c16,
	   T4."Vorname" as c17,
	   T4."Nachname" as c18,
	   T3."Group No_ 1" as c19,
	   '' as c20,
	   T4."Vorname" || ' ' || T4."Nachname" as c21,
	   T3."Leaving Date" as c22,
	   T3."Group No_ 1" as c23,
	   T3."Group No_ 2" as c24,
	   T3."Group No_ 3" as c25,
	   (extract(DAY FROM (now()) - T3."Leaving Date")) as c26,
	   CASE WHEN ((((extract(DAY FROM (now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END as c27,
	   CASE WHEN (T4."Global Dimension 1 Code" IN ('40','41','44','45')) THEN ('prod. Personal') ELSE ('unprod. Personal') END as c28,
	   (cdatetime(T1."Current Date" - cinterval(extract(DAY FROM T1."Current Date") - 1))) as c29,
	   (cdatetime(lastday(cdate(T1."Current Date")))) as c30,
	   (now()) as c31,
	   CASE WHEN (((now())) BETWEEN ((cdatetime(T1."Current Date" - cinterval(extract(DAY FROM T1."Current Date") - 1)))) AND ((cdatetime(lastday(cdate(T1."Current Date")))))) THEN (T1."Current Date") ELSE null END as c32,
	   T4."Anstellungsdatum" as c33,
	   T4."Global Dimension 1 Code" || ' - ' || T5."Name" as c34,
	   T4."Filialcode" as c35,
	   T4."Global Dimension 1 Code" as c36,
	   T5."Name" as c37,
	   '00' as c38,
	   CASE WHEN (T4."Arbeitsvertragscode" IN ('AZUBI')) THEN ('AZUBI') WHEN (T4."Arbeitsvertragscode" IN ('MEIST')) THEN ('Meister') WHEN (T3."Task Type Group" = 'UNPROD') THEN ((CASE WHEN ((((extract(DAY FROM (now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END)) ELSE ('Monteur') END as c39,
	   T4."Arbeitsvertragscode" as c40,
	   T3."Task Type Group" as c41,
	   T3."Group No_ 1" as c42,
	   T1."Time Account Value" - ((CASE WHEN (T2."No_" = 370) THEN (T1."Time Account Value") ELSE (0) END) + (CASE WHEN (T2."No_" = 379) THEN (T1."Time Account Value") ELSE (0) END) + (CASE WHEN (T2."No_" = 352) THEN (T1."Time Account Value") ELSE (0) END) + (CASE WHEN (T2."No_" = 354) THEN (T1."Time Account Value") ELSE (0) END) + 0 + (CASE WHEN (T2."No_" = 450) THEN (T1."Time Account Value") ELSE (0) END) + (CASE WHEN (T2."No_" = 355) THEN (T1."Time Account Value") ELSE (0) END) + (CASE WHEN (T2."No_" IN (350,351)) THEN (T1."Time Account Value") ELSE (0) END)) as c43,
	   T1."Current Date" as c44,
	   '1' as c45,
	   '00' as c46,
	   (CASE WHEN (T4."Arbeitsvertragscode" IN ('AZUBI')) THEN ('AZUBI') WHEN (T4."Arbeitsvertragscode" IN ('MEIST')) THEN ('Meister') WHEN (T3."Task Type Group" = 'UNPROD') THEN ((CASE WHEN ((((extract(DAY FROM (now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END)) ELSE ('Monteur') END) as c47,
	   (T4."Vorname" || ' ' || T4."Nachname") as c48,
	   CASE WHEN ((CASE WHEN (T4."Global Dimension 1 Code" IN ('40','41','44','45')) THEN ('prod. Personal') ELSE ('unprod. Personal') END) = 'prod. Personal') THEN ('produktiv') ELSE ('unproduktiv') END as c49,
	   (CASE WHEN ((((extract(DAY FROM (now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END) as c50,
	   '' as c51,
	   CASE WHEN (T1."Time Account No_" BETWEEN '300' AND '450') THEN ('abw.') ELSE null END as c52,
	   (od_left((cast_numberToString(cast_integer(T2."No_"))),3)) || ' - ' || T2."Description" as c53,
	   CASE WHEN (((od_left((cast_numberToString(cast_integer(T2."No_"))),3)) || ' - ' || T2."Description") IN ('302 - Arztbesuch')) THEN ('ARZT - Arztbesuch') ELSE (((od_left((cast_numberToString(cast_integer(T2."No_"))),3)) || ' - ' || T2."Description")) END as c54,
	   (CASE WHEN (((od_left((cast_numberToString(cast_integer(T2."No_"))),3)) || ' - ' || T2."Description") IN ('302 - Arztbesuch')) THEN ('ARZT - Arztbesuch') ELSE (((od_left((cast_numberToString(cast_integer(T2."No_"))),3)) || ' - ' || T2."Description")) END) as c55,
	   CASE WHEN ((CASE WHEN (T4."Arbeitsvertragscode" IN ('AZUBI')) THEN ('AZUBI') WHEN (T4."Arbeitsvertragscode" IN ('MEIST')) THEN ('Meister') WHEN (T3."Task Type Group" = 'UNPROD') THEN ((CASE WHEN ((((extract(DAY FROM (now()) - T3."Leaving Date"))) > 0) and (T3."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T3."Group No_ 1") END)) ELSE ('Monteur') END) IN ('AZUBI')) THEN ('Azubi') ELSE ('Monteur') END as c56,
	   0 as c57,
	   0 as c58,
	   T1."Time Account Value" as c59,
	   T4."Filialcode" as c60
from "CARLO"."import"."Employee_T" T3,
	((("CARLO"."import"."Time_Entry_T" T1 left outer join "CARLO"."import"."Time_Account_T" T2 on (T1."Time Account No_" = (cast_numberToString(cast_integer(T2."No_")))) and (T1."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Employee" T4 on (T4."Nr_" = T1."Employee No_") and (T4."Client_DB" = T1."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T5 on (T4."Global Dimension 1 Code" = T5."Code") and (T4."Client_DB" = T5."Client_DB"))
where ((T4."Nr_" = T3."No_") and (T4."Client_DB" = T3."Client_DB"))
and ((((((T2."No_" BETWEEN 299 AND 499) and (T1."Current Date" >= T4."Anstellungsdatum")) and (T1."Current Date" >= T4."Anstellungsdatum")) and ((od_year(T1."Current Date")) >= (od_year((now()))) - 2)) and (T3."Group No_ 1" IN ('AZUBI','MEISTER','MONTEUR','ANG','SB','VK'))) and ((CASE WHEN (((T4."Vorname" || ' ' || T4."Nachname") = 'Udo Litzinger') and (T1."Current Date" >= TIMESTAMP '2018-01-01 00:00:00.000')) THEN ('raus') ELSE ('rein') END) = 'rein'))
order by c2 asc,c1 asc
) D1

END SQL
COLUMN,0,Employee No
COLUMN,1,Datum
COLUMN,2,Department No
COLUMN,3,Time Account No
COLUMN,4,Time Account Value
COLUMN,5,No
COLUMN,6,Description
COLUMN,7,Description 2
COLUMN,8,Zeitdauer
COLUMN,9,Summe  (Zeitdauer) Nr.1
COLUMN,10,gesamt Stunden
COLUMN,11,Summe  (gesamt Stunden) Nr.3
COLUMN,12,Summe  (gesamt Stunden) Nr.2
COLUMN,13,Summe  (gesamt Stunden) Nr.1
COLUMN,14,Hauptbetrieb
COLUMN,15,Standort
COLUMN,16,First Name
COLUMN,17,Last Name
COLUMN,18,Monteur_Gruppe_ori_alt
COLUMN,19,Abteilung
COLUMN,20,Monteur
COLUMN,21,Leaving Date
COLUMN,22,Group No 1
COLUMN,23,Group No 2
COLUMN,24,Group No 3
COLUMN,25,Tage Heute Leaving Date
COLUMN,26,Monteur_Gruppe
COLUMN,27,produktiv/unproduktiv
COLUMN,28,Monatserster
COLUMN,29,Monatsletzter
COLUMN,30,Heute
COLUMN,31,Datum Tagesbericht
COLUMN,32,Employment Date
COLUMN,33,Kostenstelle
COLUMN,34,Filialcode_Employee
COLUMN,35,Kostenstellencode_Employee
COLUMN,36,Name_Kostenstelle
COLUMN,37,Betrieb
COLUMN,38,Monteur_Gruppe_2
COLUMN,39,Arbeitsvertragscode
COLUMN,40,Task Type Group
COLUMN,41,Monteur_Gruppe_ori
COLUMN,42,Kontrolle
COLUMN,43,Date
COLUMN,44,Company_ID
COLUMN,45,Department_ID
COLUMN,46,Mechanic_Group2
COLUMN,47,Mechanic
COLUMN,48,Mechanic_Productive
COLUMN,49,Mechanic_Group1
COLUMN,50,Order Number
COLUMN,51,Activity_Codes_Group1
COLUMN,52,Activity_Codes_Group2_ori
COLUMN,53,Activity_Codes_Group2
COLUMN,54,Activity_Desc
COLUMN,55,Mechanic_Trainee
COLUMN,56,produktiv
COLUMN,57,unproduktiv
COLUMN,58,abwesend
COLUMN,59,Filialcode_Employee