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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Zeit\fakt_Stunden_neu_3_Ryma_Abstimmung.imr
TITLE,fakt_Stunden_neu_3_Ryma_Abstimmung.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,
	   c45 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,
	   c58 as c58,
	   c59 as c59,
	   c60 as c60,
	   c61 as c61,
	   c62 as c62,
	   c63 as c63,
	   c64 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,
	   c74 as c74,
	   c75 as c75,
	   c76 as c76,
	   c77 as c77,
	   c78 as c78,
	   c79 as c79,
	   c80 as c80,
	   c81 as c81,
	   CASE WHEN ((XSUM(c73 for c6)) = 0) THEN ((c78 / (c80) / 12)) ELSE c73 END as c82,
	   c83 as c83
from 
(select c162 as c1,
	   c161 as c2,
	   c131 as c3,
	   c160 as c4,
	   c159 as c5,
	   c88 as c6,
	   c158 as c7,
	   c157 as c8,
	   c156 as c9,
	   c155 as c10,
	   c154 as c11,
	   c153 as c12,
	   c152 as c13,
	   c151 as c14,
	   c150 as c15,
	   c149 as c16,
	   c148 as c17,
	   c147 as c18,
	   c146 as c19,
	   c145 as c20,
	   c144 as c21,
	   c143 as c22,
	   c142 as c23,
	   c141 as c24,
	   c140 as c25,
	   c139 as c26,
	   c138 as c27,
	   c137 as c28,
	   c136 as c29,
	   c135 as c30,
	   c134 as c31,
	   c133 as c32,
	   c132 as c33,
	   c131 as c34,
	   '1' as c35,
	   c130 as c36,
	   c129 as c37,
	   c128 as c38,
	   c127 as c39,
	   c126 as c40,
	   c88 as c41,
	   c125 as c42,
	   c124 as c43,
	   c122 as c44,
	   c123 as c45,
	   c122 as c46,
	   c121 as c47,
	   c120 as c48,
	   c119 as c49,
	   c118 as c50,
	   c117 as c51,
	   c116 as c52,
	   c115 as c53,
	   c114 as c54,
	   c113 as c55,
	   c112 as c56,
	   c111 as c57,
	   c108 as c58,
	   c110 as c59,
	   c109 as c60,
	   c108 as c61,
	   c107 as c62,
	   c106 as c63,
	   c105 as c64,
	   c104 as c65,
	   c103 as c66,
	   c102 as c67,
	   c101 as c68,
	   c100 as c69,
	   c99 as c70,
	   c98 as c71,
	   c97 as c72,
	   c91 as c73,
	   c96 as c74,
	   c95 as c75,
	   c94 as c76,
	   c93 as c77,
	   c92 as c78,
	   c89 as c79,
	   XCOUNT(c88 for c89) as c80,
	   (c92) / (XCOUNT(c88 for c89)) / 12 as c81,
	   (c90) / (XCOUNT(c88 for c89)) as c83
from 
(select T1."Service Document No_" as c88,
	   (T1."Service Document No_" || (cast_numberToString(cast_integer(T1."Service Document Line No_")))) as c89,
	   cast_float(T5."Qty_ (Hour)") as c90,
	   CASE WHEN ((CASE WHEN (T1."Corrected" <> 1) THEN ((((cast_float(T1."Invoiced Time (Ind_ time)"))) * (1 - ((cast_float(T3."Line Discount %")) / 100)))) ELSE (0) END) < 0) THEN ((CASE WHEN (((od_left(T1."Split Rate (hrs_)",1))) = '-') THEN ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END) * -1) ELSE ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END)) END) * -1) ELSE ((CASE WHEN (((od_left(T1."Split Rate (hrs_)",1))) = '-') THEN ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END) * -1) ELSE ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END)) END)) END as c91,
	   (cast_float(T3."Quantity")) as c92,
	   T3."Qty_ to Invoice" as c93,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Document Date")) <= 63) THEN (T1."Service Document No_" || ' - ' || T4."Bill-to Name") ELSE null END as c94,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Document Date")) <= 63) THEN (T1."Document Date") ELSE null END as c95,
	   T4."Bill-to Name" as c96,
	   T2."Department No_" as c97,
	   CASE WHEN (((od_left(T1."Split Rate (hrs_)",1))) = '-') THEN ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END) * -1) ELSE ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END)) END as c98,
	   CASE WHEN (T1."Corrected" <> 1) THEN ((((cast_float(T1."Invoiced Time (Ind_ time)"))) * (1 - ((cast_float(T3."Line Discount %")) / 100)))) ELSE (0) END as c99,
	   (cast_float(T1."Invoiced Time (Ind_ time)")) as c100,
	   ((cast_float(T1."Invoiced Time (Ind_ time)"))) * (1 - ((cast_float(T3."Line Discount %")) / 100)) as c101,
	   1 - ((cast_float(T3."Line Discount %")) / 100) as c102,
	   CASE WHEN (((cast_float(T3."Line Discount %")) / 100) = 0) THEN ((CASE WHEN (((od_left(T1."Split Rate (hrs_)",1))) <> '-') THEN ((cast_float(T1."Invoiced Time (Ind_ time)"))) ELSE (0) END)) ELSE (0) END as c103,
	   (cast_float(T3."Line Discount %")) / 100 as c104,
	   T3."Line Discount Amount" as c105,
	   T3."Line Discount %" as c106,
	   T3."Quantity Disc_ %" as c107,
	   (CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END) as c108,
	   CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END as c109,
	   (CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END) * 12 as c110,
	   (cast_float(T1."Standard Time (Ind_ time)")) as c111,
	   ((cast_float(T1."Rated Qty_ (Ind_ time)"))) * 12 as c112,
	   (cast_float(T1."Rated Qty_ (Ind_ time)")) as c113,
	   T2."Employment Date" as c114,
	   CASE WHEN (((now()) - INTERVAL '001 10:00:00.000') BETWEEN ((cdatetime(T1."Document Date" - cinterval(extract(DAY FROM T1."Document Date") - 1)))) AND ((cdatetime(lastday(cdate(T1."Document Date")))))) THEN (T1."Document Date") ELSE null END as c115,
	   (now()) - INTERVAL '001 10:00:00.000' as c116,
	   (cdatetime(lastday(cdate(T1."Document Date")))) as c117,
	   (cdatetime(T1."Document Date" - cinterval(extract(DAY FROM T1."Document Date") - 1))) as c118,
	   CASE WHEN (T2."Group No_ 3" IN ('KAR','WER')) THEN ('prod. Personal') WHEN (T2."Group No_ 3" IN ('SON')) THEN ('unprod. Personal') ELSE null END as c119,
	   CASE WHEN ((T2."Leaving Date" <= TIMESTAMP '2015-01-31 00:00:00.000') and (T2."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T2."Group No_ 2") END as c120,
	   (extract(DAY FROM (now()) - T2."Leaving Date")) as c121,
	   T2."Group No_ 2" as c122,
	   T2."Group No_ 3" as c123,
	   T2."Group No_ 1" as c124,
	   T2."Leaving Date" as c125,
	   T2."First Name" || ' ' || T2."Last Name" as c126,
	   T2."First Name" as c127,
	   T2."Last Name" as c128,
	   T2."No_" as c129,
	   CASE WHEN ((od_left(T2."Department No_",2)) = '10') THEN ('LBS') WHEN ((od_left(T2."Department No_",2)) = '20') THEN ('WLS') ELSE null END as c130,
	   T1."Document Date" as c131,
	   (cast_float(T1."Labor Split Rate")) / (cast_float(T1."Labor per Hour")) as c132,
	   CASE WHEN (((od_left(T1."Split Rate (hrs_)",1))) <> '-') THEN ((cast_float(T1."Invoiced Time (Ind_ time)"))) ELSE (0) END as c133,
	   (od_left(T1."Split Rate (hrs_)",1)) as c134,
	   T1."Labor Standard Time Type" as c135,
	   T1."Split Rate (hrs_)" as c136,
	   T1."Invoiced Time Exported" as c137,
	   T1."Work Type" as c138,
	   T1."Efficiency % Resource" as c139,
	   T1."Efficiency % Position" as c140,
	   T1."Standard Time labor" as c141,
	   T1."Standard Time (hrs_)" as c142,
	   T1."Labor Split Rate" as c143,
	   T1."Labor per Hour" as c144,
	   T1."Standard Time (Ind_ time)" as c145,
	   T1."Invoiced Time (Ind_ time)" as c146,
	   T1."VIN" as c147,
	   T1."Make Code" as c148,
	   T1."No_" as c149,
	   T1."Service Job No_" as c150,
	   T1."Clock In_Out Date" as c151,
	   T1."Qty_ (Hour)" as c152,
	   T1."Rated Qty_ (Ind_ time)" as c153,
	   T1."Portion %" as c154,
	   T1."Resource No_" as c155,
	   T1."Customer No_" as c156,
	   T1."Ass_ Serv_ Ledger Entry No_" as c157,
	   T1."Service Document Line No_" as c158,
	   T1."Service Document Type" as c159,
	   T1."Document Type" as c160,
	   T1."Document No_" as c161,
	   T1."Corrected" as c162
from (((("DMS1"."dbo"."Automag GmbH$Clock In_Out Split LedgEntry" T1 left outer join "DMS1"."dbo"."Automag GmbH$Employee_T" T2 on T1."Resource No_" = T2."No_") left outer join "DMS1"."dbo"."Automag GmbH$Archived Service Line" T3 on (T1."Service Document No_" = T3."Document No_") and (T1."Service Document Line No_" = T3."Line No_")) left outer join "DMS1"."dbo"."Automag GmbH$Archived Service Header" T4 on T1."Service Document No_" = T4."No_") left outer join "DMS1"."dbo"."Automag GmbH$Labor Ledger Entry" T5 on (T1."Service Document No_" = T5."Service Order No_") and (T1."Service Document Line No_" = T5."Service Order Line No_"))
where (((T1."Document Date" >= T2."Employment Date") and (T1."Document Date" >= TIMESTAMP '2014-01-01 00:00:00.000')) and (T5."Source Code" = 'VERKAUF'))
) D1
) D4
order by c6 asc,c79 asc,c7 asc

END SQL
COLUMN,0,Corrected
COLUMN,1,Document No
COLUMN,2,Document Date
COLUMN,3,Document Type
COLUMN,4,Service Document Type
COLUMN,5,Service Document No
COLUMN,6,Service Document Line No
COLUMN,7,Ass Serv Ledger Entry No
COLUMN,8,Customer No
COLUMN,9,Resource No
COLUMN,10,Portion %
COLUMN,11,Rated Qty (ind Time)
COLUMN,12,Qty (hour)
COLUMN,13,Clock In Out Date
COLUMN,14,Service Job No
COLUMN,15,No
COLUMN,16,Make Code
COLUMN,17,Vin
COLUMN,18,Invoiced Time (ind Time)
COLUMN,19,Standard Time (ind Time)
COLUMN,20,Labor Per Hour
COLUMN,21,Labor Split Rate
COLUMN,22,Standard Time (hrs )
COLUMN,23,Standard Time Labor
COLUMN,24,Efficiency % Position
COLUMN,25,Efficiency % Resource
COLUMN,26,Work Type
COLUMN,27,Invoiced Time Exported
COLUMN,28,Split Rate (hrs )
COLUMN,29,Labor Standard Time Type
COLUMN,30,Stunde
COLUMN,31,fakt. Stunden_ori_falsch
COLUMN,32,Labor Split Rate_Zeit
COLUMN,33,Datum
COLUMN,34,Hauptbetrieb
COLUMN,35,Standort
COLUMN,36,No
COLUMN,37,Last Name
COLUMN,38,First Name
COLUMN,39,Monteur
COLUMN,40,Order Number
COLUMN,41,Leaving Date
COLUMN,42,Group No 1
COLUMN,43,Group No 2
COLUMN,44,Group No 3
COLUMN,45,Monteur_Gruppe_ori
COLUMN,46,Tage Heute Leaving Date
COLUMN,47,Monteur_Gruppe
COLUMN,48,produktiv/unproduktiv
COLUMN,49,Monatserster
COLUMN,50,Monatsletzter
COLUMN,51,Heute
COLUMN,52,Datum Tagesbericht
COLUMN,53,Employment Date
COLUMN,54,Rated Qty
COLUMN,55,Rated Qty in AW
COLUMN,56,Vorgabezeit
COLUMN,57,Vorgabezeit incl. AZ_Basis
COLUMN,58,Vorgabezeit incl. AZ in AW
COLUMN,59,Vorgabezeit incl. AZ_mit corrected <>1_ohne_Stunde-
COLUMN,60,Vorgabezeit incl. AZ
COLUMN,61,Quantity Disc %
COLUMN,62,Line Discount %
COLUMN,63,Line Discount Amount
COLUMN,64,Mengenrabatt
COLUMN,65,fakt. Stunden_falsch
COLUMN,66,Rabattberechnungszahl
COLUMN,67,fakt. Stunden_ohne_Corrected
COLUMN,68,fakt. Stunden_ori
COLUMN,69,fakt. Stunden
COLUMN,70,Vorgabezeit incl. AZ_mit corrected <>1
COLUMN,71,Department No
COLUMN,72,Vorgabezeit incl. AZ_mit corrected <>1_neu
COLUMN,73,Kunde
COLUMN,74,Datum_Monteurlisten
COLUMN,75,Auftrag_Kunde
COLUMN,76,Qty To Invoice
COLUMN,77,Quantity_archieved_Service_Line
COLUMN,78,Auftragsnummer_und_Linenumber
COLUMN,79,Anzahl_Sätze
COLUMN,80,Quantity_archieved_Service_Line/Sätze
COLUMN,81,Vorgabezeit_final
COLUMN,82,Qty (hour)_labor_ledger