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

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