C11/System/ARI/IQD/Plan/Umsatz_Planung.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Kalender_2
DATASOURCENAME,C:\GAPS_BMW\Portal\System\IQD\Plan\Umsatz_Planung.imr
TITLE,Umsatz_Planung.imr
BEGIN SQL
select T1."Zeile" as c1,
	   T1."Bezeichnung" as c2,
	   T1."Einheiten" as c3,
	   T1."Erlöse EUR" as c4,
	   T1."VAK  EUR" as c5,
	   T1."DB I  EUR" as c6,
	   T1."DB I %" as c7,
	   T1."Rechtseinheit" as c8,
	   T1."Betrieb" as c9,
	   T1."Kostenstelle" as c10,
	   T2."Ebene1" as c11,
	   T2."Ebene2" as c12,
	   T2."Ebene3" as c13,
	   T2."Ebene4" as c14,
	   T2."Ebene5" as c15,
	   (cast_float(T1."Erlöse EUR")) as c16,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0459) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0537) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0536) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0551) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c17,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0660) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0772) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0771) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0793) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c18,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0979) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0946) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0817) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0857) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c19,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0974) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0981) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0865) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0933) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c20,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0916) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0964) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0855) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0910) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c21,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.1057) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0965) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0909) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0962) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c22,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0900) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0846) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0808) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0837) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c23,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0696) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0847) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0763) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0833) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c24,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0656) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0734) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0770) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0776) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c25,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0916) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0928) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0909) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0868) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c26,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0968) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0860) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.1165) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0940) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c27,
	   CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0819) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0620) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0832) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0740) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END as c28,
	   (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0459) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0537) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0536) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0551) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) + (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0660) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0772) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0771) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0793) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) + (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0979) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0946) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0817) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0857) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) + (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0974) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0981) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0865) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0933) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) + (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0916) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0964) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0855) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0910) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) + (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.1057) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0965) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0909) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0962) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) + (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0900) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0846) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0808) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0837) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) + (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0696) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0847) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0763) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0833) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) + (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0656) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0734) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0770) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0776) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) + (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0916) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0928) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0909) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0868) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) + (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0968) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0860) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.1165) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0940) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) + (CASE WHEN (T1."Kostenstelle" = '1') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0819) WHEN (T1."Kostenstelle" = '2') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0620) WHEN (T1."Kostenstelle" = '3') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0832) WHEN (T1."Kostenstelle" = '4') THEN (((cast_float(T1."Erlöse EUR"))) * 0.0740) ELSE (((cast_float(T1."Erlöse EUR"))) * 0.0833) END) as c29,
	   2011 as c30,
	   T2."Ebene3" as c31
from "Planung_gesamt" T1,
	"Struktur_FB" T2
where (T1."Zeile" = (od_left(T2."Ebene3",4)))
and (T2."Ebene1" = 'Umsatzerlöse')

END SQL
COLUMN,0,Zeile
COLUMN,1,Bezeichnung
COLUMN,2,Einheiten
COLUMN,3,Erlöse Eur
COLUMN,4,Vak Eur
COLUMN,5,Db I Eur
COLUMN,6,Db I %
COLUMN,7,Rechtseinheit
COLUMN,8,Betrieb
COLUMN,9,Kostenstelle
COLUMN,10,Ebene1
COLUMN,11,Ebene2
COLUMN,12,Ebene3
COLUMN,13,Ebene4
COLUMN,14,Ebene5
COLUMN,15,Wert gesamt
COLUMN,16,Wert1
COLUMN,17,Wert2
COLUMN,18,Wert3
COLUMN,19,Wert4
COLUMN,20,Wert5
COLUMN,21,Wert6
COLUMN,22,Wert7
COLUMN,23,Wert8
COLUMN,24,Wert9
COLUMN,25,Wert10
COLUMN,26,Wert11
COLUMN,27,Wert12
COLUMN,28,Kontrolle
COLUMN,29,Bookkeep Date
COLUMN,30,Ebene12