COGNOS QUERY STRUCTURE,1,1 DATABASE,Navision2 DATASOURCENAME,C:\GAPS_BMW\Portal\System\IQD\Zeit\Stempelung_Auftrag.imr TITLE,Stempelung_Auftrag.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, 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, c58 as c58, c59 as c59, c60 as c60, c61 as c61, c62 as c62, RSUM(c62 for c2) as c63, RSUM(c62) 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."Line No_" as c3, T1."Order Begin Clock_ Orig_" as c4, T1."Order Begin Clock_ Actual" as c5, T1."Order Begin Entry" as c6, T1."Task Begin Entry" as c7, T1."Order End Clock_ Orig_" as c8, T1."Order End Book_ - Actual" as c9, T1."Order End Terminal No_" as c10, T1."Order End Input" as c11, T1."Task End Input" as c12, T1."Dimension 1 Begin Entry" as c13, T1."Dimension 2 Begin Entry" as c14, T1."Dimension 3 Begin Entry" as c15, T1."Dimension 4 Begin Entry" as c16, T1."Dimension 1 End Entry" as c17, T1."Dimension 2 End Entry" as c18, T1."Dimension 3 End Entry" as c19, T1."Dimension 4 End Entry" as c20, T1."TA No_ Overtime" as c21, T1."TA No_ Absences" as c22, T1."Time Acc_ No_ - Absent Day" as c23, T1."Day Object No_" as c24, T1."Delete Coming" as c25, T1."Delete Leaving" as c26, T1."Delete Errand" as c27, T1."Delete Department" as c28, T1."Delete Break" as c29, T1."Delete Absent Day" as c30, T1."Record Corrected" as c31, T1."Access Input" as c32, T2."Code" as c33, T2."Description" as c34, T3."Code" as c35, T3."Description" as c36, T3."Productive" as c37, T3."Hourtype" as c38, T3."Time Account Group No_" as c39, T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual" as c40, (extract(HOUR FROM (T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual"))) as c41, (extract(MINUTE FROM (T1."Order End Book_ - Actual" - T1."Order Begin Clock_ Actual"))) as c42, 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 c43, (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 c44, 0 as c47, CASE WHEN (T2."Code" IN ('211','212')) THEN (((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)) ELSE (0) END as c48, CASE WHEN (T2."Code" IN ('242','244')) THEN (((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)) ELSE (0) END as c49, CASE WHEN (T2."Code" IN ('112','113','114')) THEN (((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)) ELSE (0) END as c50, CASE WHEN (T2."Code" IN ('216')) THEN (((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)) ELSE (0) END as c51, CASE WHEN (T2."Code" IN ('331')) THEN (((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)) ELSE (0) END as c52, CASE WHEN (T2."Code" IN ('336')) THEN (((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)) ELSE (0) END as c53, CASE WHEN (T2."Code" IN ('245')) THEN (((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)) ELSE (0) END as c54, CASE WHEN (T2."Code" IN ('243')) THEN (((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)) ELSE (0) END as c55, (0) as c56, 0 as c57, CASE WHEN (T2."Code" IN ('332')) THEN (((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)) ELSE (0) END as c58, CASE WHEN (T2."Code" IN ('111')) THEN (((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)) ELSE (0) END as c59, 0 as c60, CASE WHEN (T2."Code" IN ('150')) THEN (((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)) ELSE (0) END as c61, (CASE WHEN (T2."Code" IN ('111')) THEN (((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)) ELSE (0) END) + 0 + (CASE WHEN (T2."Code" IN ('150')) THEN (((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)) ELSE (0) END) as c62, '1' as c65, 'LBS' as c66, T4."First Name" as c67, T4."Last Name" as c68, '' as c69, '' as c70, T1."Order Begin Entry" as c71, T4."First Name" || ' ' || T4."Last Name" as c72, 0 + (CASE WHEN (T2."Code" IN ('211','212')) THEN (((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)) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('242','244')) THEN (((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)) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('112','113','114')) THEN (((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)) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('216')) THEN (((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)) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('331')) THEN (((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)) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('336')) THEN (((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)) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('245')) THEN (((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)) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('243')) THEN (((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)) ELSE (0) END) + ((0)) as c73 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_") where (T1."Current Date" BETWEEN TIMESTAMP '2011-03-01 00:00:00.000' AND TIMESTAMP '2011-03-31 00:00:00.000') order by c2 asc ) D1 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,Vorabdiagnosen COLUMN,47,allg. Werkstattarb. COLUMN,48,Meistervertr. COLUMN,49,Wartezeit COLUMN,50,Fahrtätigkeit COLUMN,51,Schulung intern COLUMN,52,Besprechung COLUMN,53,Erstellung Verkaufsaufträge COLUMN,54,Servicemobil COLUMN,55,W-Zeit Azubi COLUMN,56,Car Jockey COLUMN,57,Schulung extern COLUMN,58,extern COLUMN,59,GWL COLUMN,60,intern COLUMN,61,produktiv COLUMN,62,Summe (produktiv) Nr.2 COLUMN,63,Summe (produktiv) Nr.1 COLUMN,64,Hauptbetrieb COLUMN,65,Standort COLUMN,66,First Name COLUMN,67,Last Name COLUMN,68,Monteur_Gruppe COLUMN,69,Abteilung COLUMN,70,Order Number COLUMN,71,Monteur COLUMN,72,W-variabel Stunden