C11/System/ARIntelligence/IQD/Zeit/Zeit_Anwesenheit_Serviceberater.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Zeit\Zeit_Anwesenheit_Serviceberater.imr
TITLE,Zeit_Anwesenheit_Serviceberater.imr
BEGIN SQL
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 c20,
	   CASE WHEN (T2."No_" = 370) 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 c22,
	   CASE WHEN (T2."No_" = 352) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c23,
	   CASE WHEN (T2."No_" = 354) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c24,
	   0 as c25,
	   CASE WHEN (T2."No_" = 450) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c26,
	   CASE WHEN (T2."No_" = 355) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c27,
	   CASE WHEN (T2."No_" IN (350,351)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c28,
	   (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 c29,
	   '1' as c30,
	   CASE WHEN ((od_left(T1."Department No_",2)) = '10') THEN ('10') WHEN ((od_left(T1."Department No_",2)) = '20') THEN ('20') ELSE null END as c31,
	   T3."First Name" as c32,
	   T3."Last Name" as c33,
	   '' as c34,
	   '' as c35,
	   '' as c36,
	   T3."First Name" || ' ' || T3."Last Name" as c37,
	   CASE WHEN (T2."No_" IN (378)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c38,
	   CASE WHEN (T2."No_" IN (200)) THEN (((cast_float(T1."Time Account Value")))) ELSE (0) END as c39,
	   T3."Service Advisor" as c40,
	   1 as c41,
	   T1."Current Date" as c42,
	   T4."Resource Group No_" as c43,
	   CASE WHEN (T4."Resource Group No_" = 'SB') THEN ('SB') ELSE ('Rest') END as c44
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_") left outer join "DMS1"."dbo"."Automag GmbH$Resource" T4 on T3."No_" = T4."No_")
where ((T2."No_" = 100) and (T3."Service Advisor" = 1))
order by c2 asc,c1 asc

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,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
COLUMN,30,Standort
COLUMN,31,First Name
COLUMN,32,Last Name
COLUMN,33,Monteur_Gruppe
COLUMN,34,Abteilung
COLUMN,35,Order Number
COLUMN,36,Serviceberater
COLUMN,37,Zeitausgleich
COLUMN,38,Überstunden
COLUMN,39,Service Advisor
COLUMN,40,Anwesenheitstage
COLUMN,41,Invoice Date
COLUMN,42,Resource Group No
COLUMN,43,SB_Gruppe