COGNOS QUERY
STRUCTURE,1,1
DATABASE,EDS_1
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\benutzte_verr_Zeit_LG.imr
TITLE,benutzte_verr_Zeit_LG.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,
	   c20 as c20,
	   c21 as c21,
	   c22 as c22,
	   RSUM(c22 for c1) as c23,
	   RSUM(c22 for c1,c10) as c24,
	   RSUM(c22) 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,
	   RSUM(c44 for c1) as c45,
	   RSUM(c44) as c46,
	   c47 as c47,
	   c48 as c48,
	   RSUM(c48 for c1) as c49,
	   RSUM(c48) as c50,
	   c51 as c51,
	   c52 as c52,
	   c53 as c53,
	   c54 as c54,
	   c55 as c55,
	   c56 as c56,
	   c57 as c57
from 
(select T1."Employee No_" as c1,
	   T1."Current Date" as c2,
	   T1."Department No_" as c3,
	   T1."Order No_" as c4,
	   T1."Task No_" as c5,
	   T1."Dimension 1" as c6,
	   T1."Dimension 2" as c7,
	   T1."Dimension 3" as c8,
	   T1."Dimension 4" as c9,
	   T1."Time Account No_" as c10,
	   T1."Time Account Value" as c11,
	   T1."TA Class (General)" as c12,
	   T1."TA Class (Statistics)" as c13,
	   T1."TA Class (Missing Day)" as c14,
	   T1."TA Class (Time Processing)" as c15,
	   T1."TA Class (Individual)" as c16,
	   T1."TA Class (Employee Info)" as c17,
	   T1."TA Class (Vacation Reduction)" as c18,
	   T1."TA Formatting" as c19,
	   T1."Generate Function" as c20,
	   T1."Record protected" as c21,
	   (cast_float(T1."Time Account Value")) as c22,
	   T1."Current Date" as c26,
	   '1' as c27,
	   T2."Filialcode" as c28,
	   T2."Filialcode" as c29,
	   CASE WHEN (T2."Filialcode" IN ('00','05')) THEN ('Königsberger Str.') WHEN (T2."Filialcode" IN ('10','15')) THEN ('Kevelaer') WHEN (T2."Filialcode" IN ('20','25')) THEN ('Brunnenstr.') WHEN (T2."Filialcode" IN ('30','35')) THEN ('Weilerswist') WHEN (T2."Filialcode" IN ('50','55')) THEN ('Benrath') WHEN (T2."Filialcode" IN ('40','45')) THEN ('Ratingen') ELSE null END as c30,
	   T2."Global Dimension 1 Code" as c31,
	   T3."Group No_ 1" as c32,
	   T3."Leaving Date" as c33,
	   (extract(DAY FROM (now()) - T3."Leaving Date")) as c34,
	   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 c35,
	   T2."Nr_" as c36,
	   CASE WHEN (T3."Group No_ 1" IN ('AZUBI 1','AZUBI 2','AZUBI 3','AZUBI 4')) THEN ('AZUBI') WHEN (T3."Group No_ 1" IN ('MONTEUR')) THEN ('Monteur') ELSE ('andere Mitarbeiter') END as c37,
	   T2."Vorname" as c38,
	   T2."Nachname" as c39,
	   T2."Nachname" as c40,
	   T1."Order No_" as c41,
	   T4."Name" as c42,
	   T2."Global Dimension 1 Code" || ' - ' || T4."Name" as c43,
	   CASE WHEN (T1."Time Account No_" IN ('6121','6122','6123')) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c44,
	   CASE WHEN (T1."Time Account No_" IN ('6201','6202','6203')) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c47,
	   (round((CASE WHEN (T1."Time Account No_" IN ('6121','6122','6123')) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END),2,0)) as c48,
	   T5."Description" as c51,
	   CASE WHEN (T1."Time Account No_" = '100') THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c52,
	   (CASE WHEN (T1."Time Account No_" IN ('6121','6122','6123')) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) as c53,
	   T2."Arbeitsvertragscode" as c54,
	   CASE WHEN (T1."Time Account No_" = '1300') THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c55,
	   CASE WHEN (T1."Time Account No_" = '1200') THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c56,
	   (CASE WHEN (T1."Time Account No_" = '100') THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) - (CASE WHEN (T1."Time Account No_" = '1300') THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END) as c57
from (("DE0682"."dbo"."Tretter Bad Bergzabern$Archiv_ Werkstattkopf" T7 left outer join (((("DE0682"."dbo"."Tretter Bad Bergzabern$Time Entry_T" T1 left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Employee" T2 on T2."Nr_" = T1."Employee No_") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Employee_T" T3 on T2."Nr_" = T3."No_") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Kostenstelle" T4 on T2."Global Dimension 1 Code" = T4."Code") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Time Account_T" T5 on T1."Time Account No_" = (cast_numberToString(cast_integer(T5."No_")))) on T1."Order No_" = T7."Nr_") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Employee" T6 on T6."Nr_" = T7."Serviceberaternr_")
where (((T1."Current Date" >= TIMESTAMP '2013-01-01 00:00:00.000') and (T3."Group No_ 1" IN ('AZUBI 1','AZUBI 2','AZUBI 3','AZUBI 4','HILFSLOHN','MONTEUR'))) and (T6."Global Dimension 1 Code" IN ('40','41','42','62')))
order by c1 asc,c10 asc
) D1

END SQL
COLUMN,0,Employee No
COLUMN,1,Current Date
COLUMN,2,Department No
COLUMN,3,Order No
COLUMN,4,Task No
COLUMN,5,Dimension 1
COLUMN,6,Dimension 2
COLUMN,7,Dimension 3
COLUMN,8,Dimension 4
COLUMN,9,Time Account No
COLUMN,10,Time Account Value
COLUMN,11,Ta Class (general)
COLUMN,12,Ta Class (statistics)
COLUMN,13,Ta Class (missing Day)
COLUMN,14,Ta Class (time Processing)
COLUMN,15,Ta Class (individual)
COLUMN,16,Ta Class (employee Info)
COLUMN,17,Ta Class (vacation Reduction)
COLUMN,18,Ta Formatting
COLUMN,19,Generate Function
COLUMN,20,Record Protected
COLUMN,21,Zeit
COLUMN,22,Summe  (Zeit) Nr.3
COLUMN,23,Summe  (Zeit) Nr.2
COLUMN,24,Summe  (Zeit) Nr.1
COLUMN,25,Datum
COLUMN,26,Hauptbetrieb
COLUMN,27,Filialcode_Monteur
COLUMN,28,Standort
COLUMN,29,Betrieb
COLUMN,30,Kostenstellencode_Monteur
COLUMN,31,Monteur_Gruppe_ori
COLUMN,32,Leaving Date
COLUMN,33,Tage Heute Leaving Date
COLUMN,34,Monteur_Gruppe
COLUMN,35,Nr_Monteur
COLUMN,36,Monteur_Gruppe_2
COLUMN,37,Vorname_Monteur
COLUMN,38,Nachname_Monteur
COLUMN,39,Monteur
COLUMN,40,Order Number
COLUMN,41,Name_Kostenstelle_Monteur
COLUMN,42,Kostenstelle
COLUMN,43,benutzte Zeit
COLUMN,44,Summe  (benutzte Zeit) Nr.2
COLUMN,45,Summe  (benutzte Zeit) Nr.1
COLUMN,46,verr. Zeit
COLUMN,47,ben. Zeit gerundet
COLUMN,48,Summe  (ben. Zeit gerundet) Nr.2
COLUMN,49,Summe  (ben. Zeit gerundet) Nr.1
COLUMN,50,Description
COLUMN,51,Anwesenheit
COLUMN,52,produktiv
COLUMN,53,Arbeitsvertragscode
COLUMN,54,Pause
COLUMN,55,Sollzeit
COLUMN,56,Anwes. Netto