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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\GAPS_BMW\Portal\System\IQD\Zeit\fakt_Stunden.imr
TITLE,fakt_Stunden.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,
	   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,
	   RSUM(c44 for c2) 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
from 
(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,
	   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,
	   RSUM(c44) 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
from 
(select c107 as c1,
	   c66 as c2,
	   c106 as c3,
	   c105 as c4,
	   c104 as c5,
	   c60 as c6,
	   c103 as c7,
	   c102 as c8,
	   c101 as c9,
	   c100 as c10,
	   c99 as c11,
	   c98 as c12,
	   c97 as c13,
	   c96 as c14,
	   c95 as c15,
	   c94 as c16,
	   c93 as c17,
	   c92 as c18,
	   c91 as c19,
	   c90 as c20,
	   c89 as c21,
	   c88 as c22,
	   c87 as c23,
	   c86 as c24,
	   c85 as c25,
	   c84 as c26,
	   c83 as c27,
	   c82 as c28,
	   c81 as c29,
	   c80 as c30,
	   c79 as c31,
	   c78 as c32,
	   c77 as c33,
	   c76 as c34,
	   c75 as c35,
	   c74 as c36,
	   c73 as c37,
	   c72 as c38,
	   c71 as c39,
	   c70 as c40,
	   c69 as c41,
	   c68 as c42,
	   c67 as c43,
	   c65 as c44,
	   '1' as c47,
	   'LBS' as c48,
	   c64 as c49,
	   c63 as c50,
	   '' as c51,
	   '' as c52,
	   c60 as c53,
	   c62 as c54,
	   c61 as c55,
	   XCOUNT(c107 for c60) as c56,
	   (c61) / (XCOUNT(c107 for c60)) as c57
from 
(select T1."Order Begin Entry" as c60,
	   (cast_float(T5."Qty_ (Hour)")) as c61,
	   T4."First Name" || ' ' || T4."Last Name" as c62,
	   T4."Last Name" as c63,
	   T4."First Name" as c64,
	   (CASE WHEN ((((extract(HOUR FROM (T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual")))) * 60) >= 0) THEN (((extract(HOUR FROM (T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual")))) * 60 + ((extract(MINUTE FROM (T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual"))))) ELSE (0) END) / 60 as c65,
	   T1."Current Date" as c66,
	   CASE WHEN ((((extract(HOUR FROM (T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual")))) * 60) >= 0) THEN (((extract(HOUR FROM (T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual")))) * 60 + ((extract(MINUTE FROM (T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual"))))) ELSE (0) END as c67,
	   (extract(MINUTE FROM (T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual"))) as c68,
	   (extract(HOUR FROM (T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual"))) as c69,
	   T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual" as c70,
	   T3."Time Account Group No_" as c71,
	   T3."Hourtype" as c72,
	   T3."Productive" as c73,
	   T3."Description" as c74,
	   T3."Code" as c75,
	   T2."Description" as c76,
	   T2."Code" as c77,
	   T1."Access Input" as c78,
	   T1."Record Corrected" as c79,
	   T1."Delete Absent Day" as c80,
	   T1."Delete Break" as c81,
	   T1."Delete Department" as c82,
	   T1."Delete Errand" as c83,
	   T1."Delete Leaving" as c84,
	   T1."Delete Coming" as c85,
	   T1."Day Object No_" as c86,
	   T1."Time Acc_ No_ - Absent Day" as c87,
	   T1."TA No_ Absences" as c88,
	   T1."TA No_ Overtime" as c89,
	   T1."Dimension 4 End Entry" as c90,
	   T1."Dimension 3 End Entry" as c91,
	   T1."Dimension 2 End Entry" as c92,
	   T1."Dimension 1 End Entry" as c93,
	   T1."Dimension 4 Begin Entry" as c94,
	   T1."Dimension 3 Begin Entry" as c95,
	   T1."Dimension 2 Begin Entry" as c96,
	   T1."Dimension 1 Begin Entry" as c97,
	   T1."Task End Input" as c98,
	   T1."Order End Input" as c99,
	   T1."Order End Terminal No_" as c100,
	   T1."Order End Book_ - Actual" as c101,
	   T1."Order End Clock_ Orig_" as c102,
	   T1."Task Begin Entry" as c103,
	   T1."Order Begin Clock_ Actual" as c104,
	   T1."Order Begin Clock_ Orig_" as c105,
	   T1."Line No_" as c106,
	   T1."Employee No_" as c107
from (((("DMS1"."dbo"."Automag GmbH$Time Clocking_T" T1 left outer join "DMS1"."dbo"."Automag GmbH$Task Type" T2 on T1."Dimension 1 End Entry" = T2."Code") left outer join "DMS1"."dbo"."Automag GmbH$Task Statistic Group" T3 on T2."Task Statistic Group" = T3."Code") left outer join "DMS1"."dbo"."Automag GmbH$Employee" T4 on T1."Employee No_" = T4."No_") left outer join "DMS1"."dbo"."Automag GmbH$Labor Ledger Entry" T5 on (T1."Order End Input" = T5."Service Order No_") and (T1."Task End Input" = (cast_numberToString(cast_integer(T5."Service Order Line No_")))))
where ((T1."Current Date" BETWEEN TIMESTAMP '2011-03-01 00:00:00.000' AND TIMESTAMP '2011-03-31 00:00:00.000') and (T1."Order Begin Entry" <> ' '))
) D1
order by c2 asc,c6 asc
) D4
) D3

END SQL
COLUMN,0,Employee No
COLUMN,1,Current Date
COLUMN,2,Line No
COLUMN,3,Order Begin Clock Orig
COLUMN,4,Order Begin Clock Actual
COLUMN,5,Order Begin Entry
COLUMN,6,Task Begin Entry
COLUMN,7,Order End Clock Orig
COLUMN,8,Order End Book - Actual
COLUMN,9,Order End Terminal No
COLUMN,10,Order End Input
COLUMN,11,Task End Input
COLUMN,12,Dimension 1 Begin Entry
COLUMN,13,Dimension 2 Begin Entry
COLUMN,14,Dimension 3 Begin Entry
COLUMN,15,Dimension 4 Begin Entry
COLUMN,16,Dimension 1 End Entry
COLUMN,17,Dimension 2 End Entry
COLUMN,18,Dimension 3 End Entry
COLUMN,19,Dimension 4 End Entry
COLUMN,20,Ta No Overtime
COLUMN,21,Ta No Absences
COLUMN,22,Time Acc No - Absent Day
COLUMN,23,Day Object No
COLUMN,24,Delete Coming
COLUMN,25,Delete Leaving
COLUMN,26,Delete Errand
COLUMN,27,Delete Department
COLUMN,28,Delete Break
COLUMN,29,Delete Absent Day
COLUMN,30,Record Corrected
COLUMN,31,Access Input
COLUMN,32,Code
COLUMN,33,Description
COLUMN,34,Code
COLUMN,35,Description
COLUMN,36,Productive
COLUMN,37,Hourtype
COLUMN,38,Time Account Group No
COLUMN,39,Zeitdauer_ori
COLUMN,40,Stunden
COLUMN,41,Minuten
COLUMN,42,Zeitdauer_Minuten
COLUMN,43,Zeitdauer
COLUMN,44,Summe  (Zeitdauer) Nr.2
COLUMN,45,Summe  (Zeitdauer) Nr.1
COLUMN,46,Hauptbetrieb
COLUMN,47,Standort
COLUMN,48,First Name
COLUMN,49,Last Name
COLUMN,50,Monteur_Gruppe
COLUMN,51,Abteilung
COLUMN,52,Order Number
COLUMN,53,Monteur
COLUMN,54,Qty (hour)
COLUMN,55,Berechnungszahl
COLUMN,56,fakt. Stunden