COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\GAPS_BMW\Portal\System\IQD\Zeit\Zeit_1.imr
TITLE,Zeit_1.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
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 (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 c21,
	   CASE WHEN (T2."No_" = 370) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c25,
	   CASE WHEN (T2."No_" = 379) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c26,
	   CASE WHEN (T2."No_" = 352) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c27,
	   CASE WHEN (T2."No_" = 354) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c28,
	   0 as c29,
	   CASE WHEN (T2."No_" = 450) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c30,
	   CASE WHEN (T2."No_" = 355) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c31,
	   CASE WHEN (T2."No_" IN (350,351)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c32,
	   (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 c33,
	   '1' as c34,
	   'LBS' as c35,
	   T3."First Name" as c36,
	   T3."Last Name" as c37,
	   '' as c38,
	   '' as c39,
	   '' as c40,
	   T3."First Name" || ' ' || T3."Last Name" as c41,
	   CASE WHEN (T2."No_" IN (378)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c42,
	   CASE WHEN (T2."No_" IN (200)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c43
from (("DMS1"."dbo"."Automag GmbH$Time Entry_T" T1 left outer join "DMS1"."dbo"."Automag GmbH$Time Account_T" T2 on T1."Time Account No_" = (cast_numberToString(cast_integer(T2."No_")))) left outer join "DMS1"."dbo"."Automag GmbH$Employee" T3 on T1."Employee No_" = T3."No_")
order by c2 asc,c1 asc
) D1

END SQL
COLUMN,0,Employee No
COLUMN,1,Current Date
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,krank
COLUMN,25,Wehr- /Zivildienst
COLUMN,26,Sonderurlaub
COLUMN,27,Berufsschule
COLUMN,28,Innung Azubi
COLUMN,29,Feiertag
COLUMN,30,Schulung extern
COLUMN,31,Urlaub
COLUMN,32,W-fix Stunden
COLUMN,33,Hauptbetrieb
COLUMN,34,Standort
COLUMN,35,First Name
COLUMN,36,Last Name
COLUMN,37,Monteur_Gruppe
COLUMN,38,Abteilung
COLUMN,39,Order Number
COLUMN,40,Monteur
COLUMN,41,Zeitausgleich
COLUMN,42,Überstunden