GAPS/Portal/System/IQD/Zeiten/Zeit_2_Aftersales_Germersheim.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,EDS_1
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\Zeit_2_Aftersales_Germersheim.imr
TITLE,Zeit_2_Aftersales_Germersheim.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,
	   c10 as c10,
	   c11 as c11,
	   c12 as c12,
	   c13 as c13,
	   c14 as c14,
	   c15 as c15,
	   c16 as c16,
	   c17 as c17,
	   c18 as c18,
	   c19 as c19,
	   RSUM(c19) as c20,
	   c21 as c21,
	   RSUM(c21 for c2) as c22,
	   RSUM(c21) as c23,
	   RSUM(c21) 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,
	   c61 as c61,
	   c62 as c62,
	   c63 as c63,
	   c64 as c64,
	   c65 as c65,
	   c66 as c66,
	   c67 as c67,
	   c68 as c68,
	   c69 as c69,
	   c70 as c70,
	   c71 as c71,
	   c72 as c72,
	   c73 as c73
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,
	   T1."TA Class (General)" as c6,
	   T1."TA Class (Statistics)" as c7,
	   T1."TA Class (Missing Day)" 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."Generate 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 c21,
	   CASE WHEN (T2."No_" = 379) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c25,
	   0 as c26,
	   (CASE WHEN (T2."No_" = 370) 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) as c27,
	   '1' as c28,
	   T3."Filialcode" as c29,
	   T3."Vorname" as c30,
	   T3."Nachname" as c31,
	   CASE WHEN (T3."Global Dimension 1 Code" IN ('40')) THEN ('Mechanik') WHEN (T3."Global Dimension 1 Code" IN ('41')) THEN ('Karosserie') WHEN (T3."Global Dimension 1 Code" IN ('42')) THEN ('Lack') ELSE null END as c32,
	   '' as c33,
	   '' as c34,
	   T3."Vorname" || ' ' || T3."Nachname" as c35,
	   CASE WHEN (T2."No_" IN (200)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c36,
	   T4."Leaving Date" as c37,
	   T4."Group No_ 1" as c38,
	   T4."Group No_ 2" as c39,
	   T4."Group No_ 3" as c40,
	   (extract(DAY FROM (now()) - T4."Leaving Date")) as c41,
	   CASE WHEN ((((extract(DAY FROM (now()) - T4."Leaving Date"))) > 0) and (T4."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE ((CASE WHEN (T3."Global Dimension 1 Code" IN ('40')) THEN ('Mechanik') WHEN (T3."Global Dimension 1 Code" IN ('41')) THEN ('Karosserie') WHEN (T3."Global Dimension 1 Code" IN ('42')) THEN ('Lack') ELSE null END)) END as c42,
	   CASE WHEN (T4."Group No_ 3" IN ('KAR','WER')) THEN ('prod. Personal') WHEN (T4."Group No_ 3" IN ('SON')) THEN ('unprod. Personal') ELSE null END as c43,
	   (cdatetime(T1."Current Date" - cinterval(extract(DAY FROM T1."Current Date") - 1))) as c44,
	   (cdatetime(lastday(cdate(T1."Current Date")))) as c45,
	   (now()) as c46,
	   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 c47,
	   T3."Anstellungsdatum" as c48,
	   CASE WHEN (T2."No_" = 355) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c49,
	   CASE WHEN (T2."No_" IN (370,371,372)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c50,
	   CASE WHEN (T2."No_" IN (302)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c51,
	   CASE WHEN (T2."No_" IN (350,351)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c52,
	   CASE WHEN (T2."No_" IN (352,353)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c53,
	   CASE WHEN (T2."No_" IN (299,300,301,340)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c54,
	   CASE WHEN (T2."No_" IN (354)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c55,
	   CASE WHEN (T2."No_" IN (450)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c56,
	   CASE WHEN (T2."No_" IN (378)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c57,
	   T3."Global Dimension 1 Code" || ' - ' || T5."Name" as c58,
	   T3."Filialcode" as c59,
	   T3."Global Dimension 1 Code" as c60,
	   T5."Name" as c61,
	   CASE WHEN (T3."Filialcode" IN ('00','05')) THEN ('Königsberger Str.') WHEN (T3."Filialcode" IN ('10','15')) THEN ('Kevelaer') WHEN (T3."Filialcode" IN ('20','25')) THEN ('Brunnenstr.') WHEN (T3."Filialcode" IN ('30','35')) THEN ('Weilerswist') WHEN (T3."Filialcode" IN ('50','55')) THEN ('Benrath') WHEN (T3."Filialcode" IN ('40','45')) THEN ('Ratingen') ELSE null END as c62,
	   CASE WHEN (T3."Arbeitsvertragscode" = 'AZUBI') THEN ('AZUBI') WHEN (T3."Arbeitsvertragscode" IN ('MECH','LACK')) THEN ('Monteur') ELSE ('andere Mitarbeiter') END as c63,
	   T3."Arbeitsvertragscode" as c64,
	   T1."Employee No_" || ' - ' || (T3."Vorname" || ' ' || T3."Nachname") as c65,
	   (CASE WHEN (T2."No_" = 355) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."No_" IN (370,371,372)) 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 (352,353)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) as c66,
	   CASE WHEN (((CASE WHEN (T2."No_" IN (350,351)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) BETWEEN 4.1 AND 8) or ((CASE WHEN (T2."No_" IN (352,353)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) BETWEEN 4.1 AND 8)) THEN (1) WHEN (((CASE WHEN (T2."No_" IN (350,351)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) BETWEEN 0.1 AND 4.09) or ((CASE WHEN (T2."No_" IN (352,353)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) BETWEEN 0.1 AND 4.09)) THEN (0.5) ELSE (0) END as c67,
	   CASE WHEN ((CASE WHEN (T2."No_" IN (370,371,372)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) BETWEEN 4.1 AND 8) THEN (1) WHEN ((CASE WHEN (T2."No_" IN (370,371,372)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) BETWEEN 0.1 AND 4.09) THEN (0.5) ELSE (0) END as c68,
	   CASE WHEN ((CASE WHEN (T2."No_" = 355) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) BETWEEN 4.1 AND 8) THEN (1) WHEN ((CASE WHEN (T2."No_" = 355) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) BETWEEN 0.1 AND 4.09) THEN (0.5) ELSE (0) END as c69,
	   CASE WHEN ((T1."Employee No_" || ' - ' || (T3."Vorname" || ' ' || T3."Nachname")) IN ('28 - Michael Gißke','69 - Jan Conradi','18 - Jessica Grunert','256 - Michael Scherer','31 - Olaf Wozniak','39 - Thomas Friedrich','15 - Henry Kingler','245 - Marcel Stoof','12 - Dominik Scherz','58 - Gardo Brieseck','111 - Fred Frenzel','258 - Lutz Pittelkow','71 - Dirk Jagelmann','143 - Volker Hellie','142 - Peter Löchel','147 - Helmer Schröter','195 - Marcel Liebers','145 - Olaf Gladewitz','144 - Joachim Riedel','146 - Klaus Schmiedel','189 - Carolin Hof','103 - Petra Recklies','192 - Leif Haseloff','190 - Katja Kanzenbach','139 - Klaus-Dieter Klitsch','133 - Jeanette Paasch','140 - Heike Ihms','134 - Nadine Reiter')) THEN ('Serviceberater') ELSE ('Serviceberater') END as c70,
	   T3."Serviceberater" as c71,
	   T1."Current Date" as c72,
	   CASE WHEN ((T3."Global Dimension 1 Code" || ' - ' || T5."Name") = '40 - Mechanik') THEN ('3') WHEN ((T3."Global Dimension 1 Code" || ' - ' || T5."Name") = '41 - Karosserie') THEN ('4') WHEN ((T3."Global Dimension 1 Code" || ' - ' || T5."Name") LIKE '42%') THEN ('5') ELSE null END as c73
from "DE0682"."dbo"."Tretter Germersheim$Employee_T" T4,
	((("DE0682"."dbo"."Tretter Germersheim$Time Entry_T" T1 left outer join "DE0682"."dbo"."Tretter Germersheim$Time Account_T" T2 on T1."Time Account No_" = (cast_numberToString(cast_integer(T2."No_")))) left outer join "DE0682"."dbo"."Tretter Germersheim$Employee" T3 on T3."Nr_" = T1."Employee No_") left outer join "DE0682"."dbo"."Tretter Germersheim$Kostenstelle" T5 on T3."Global Dimension 1 Code" = T5."Code")
where (T3."Nr_" = T4."No_")
and ((((T1."Current Date" >= T3."Anstellungsdatum") and (T1."Current Date" >= TIMESTAMP '2012-01-01 00:00:00.000')) and (T3."Serviceberater" = 1)) and (((CASE WHEN (T2."No_" = 355) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) + (CASE WHEN (T2."No_" IN (370,371,372)) 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 (352,353)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END)) <> 0))
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,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,Summe  (Zeitdauer) Nr.1
COLUMN,20,gesamt Stunden
COLUMN,21,Summe  (gesamt Stunden) Nr.3
COLUMN,22,Summe  (gesamt Stunden) Nr.2
COLUMN,23,Summe  (gesamt Stunden) Nr.1
COLUMN,24,Wehr- /Zivildienst
COLUMN,25,Innung Azubi
COLUMN,26,W-fix Stunden
COLUMN,27,Hauptbetrieb
COLUMN,28,Standort
COLUMN,29,First Name
COLUMN,30,Last Name
COLUMN,31,Monteur_Gruppe_ori
COLUMN,32,Abteilung
COLUMN,33,Order Number
COLUMN,34,Monteur_ori
COLUMN,35,Überstunden
COLUMN,36,Leaving Date
COLUMN,37,Group No 1
COLUMN,38,Group No 2
COLUMN,39,Group No 3
COLUMN,40,Tage Heute Leaving Date
COLUMN,41,Monteur_Gruppe
COLUMN,42,produktiv/unproduktiv
COLUMN,43,Monatserster
COLUMN,44,Monatsletzter
COLUMN,45,Heute
COLUMN,46,Datum Tagesbericht
COLUMN,47,Employment Date
COLUMN,48,Schulung extern
COLUMN,49,Krank
COLUMN,50,Arzt
COLUMN,51,Urlaub
COLUMN,52,Sonderurlaub
COLUMN,53,Fehlstunden
COLUMN,54,Berufsschule
COLUMN,55,Feiertag
COLUMN,56,Zeitausgleich
COLUMN,57,Kostenstelle_ori
COLUMN,58,Filialcode_Employee
COLUMN,59,Kostenstellencode_Employee
COLUMN,60,Name_Kostenstelle
COLUMN,61,Betrieb
COLUMN,62,Monteur_Gruppe_2
COLUMN,63,Arbeitsvertragscode
COLUMN,64,Serviceberater
COLUMN,65,Abwesenheiten Serviceberater
COLUMN,66,Urlaubstage
COLUMN,67,Kranktage
COLUMN,68,Schultage
COLUMN,69,Standort_1
COLUMN,70,Serviceberater_KZ_Employee
COLUMN,71,Invoice Date
COLUMN,72,Kostenstelle