GAPS/Portal/System/IQD/Planung/Umsatz_Einsatz_2019.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Planung_2012
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Planung\Umsatz_Einsatz_2019.imr
TITLE,Umsatz_Einsatz_2019.imr
BEGIN SQL
select T1."Art" as c1,
	   T1."Feld2" as c2,
	   T1."Acct Nr_ori" as c3,
	   T1."Ledger Accts Name" as c4,
	   T1."Marke_1" as c5,
	   T1."Modell" as c6,
	   T1."Abnehmergruppe" as c7,
	   T1."Ebene1" as c8,
	   T1."Ebene2" as c9,
	   T1."Ebene3" as c10,
	   T1."Ebene4" as c11,
	   T1."Ebene5" as c12,
	   T1."Ebene6" as c13,
	   T1."Jahr" as c14,
	   T1."Mandant" as c15,
	   T1."Kürzel" as c16,
	   T1."KST" as c17,
	   T1."Marke" as c18,
	   T1."Plan" as c19,
	   T1."Stück" as c20,
	   CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END as c21,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Jan") ELSE (T1."Plan" / 12) END as c22,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Feb") ELSE (T1."Plan" / 12) END as c23,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Mrz") ELSE (T1."Plan" / 12) END as c24,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Apr") ELSE (T1."Plan" / 12) END as c25,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Mai") ELSE (T1."Plan" / 12) END as c26,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Jun") ELSE (T1."Plan" / 12) END as c27,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Jul") ELSE (T1."Plan" / 12) END as c28,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Aug") ELSE (T1."Plan" / 12) END as c29,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Sep") ELSE (T1."Plan" / 12) END as c30,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Okt") ELSE (T1."Plan" / 12) END as c31,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Nov") ELSE (T1."Plan" / 12) END as c32,
	   CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Dez") ELSE (T1."Plan" / 12) END as c33,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c34,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c35,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c36,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c37,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c38,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c39,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c40,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c41,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c42,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c43,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c44,
	   (CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END) / 12 as c45,
	   CASE WHEN (T1."Ebene2" IN ('EW Gebrauchtwagen','EW Gebrauchtwagen
')) THEN ('EW Gebrauchtwagen') WHEN (T1."Ebene2" IN ('VE Gebrauchtwagen','VE Gebrauchtwagen
')) THEN ('VE Gebrauchtwagen') ELSE (T1."Ebene2") END as c46,
	   T2."Kst" as c47,
	   T2."Summe" as c48,
	   T2."ID" as c49,
	   T2."Jahr" as c50,
	   T2."Jan" as c51,
	   T2."Feb" as c52,
	   T2."Mrz" as c53,
	   T2."Apr" as c54,
	   T2."Mai" as c55,
	   T2."Jun" as c56,
	   T2."Jul" as c57,
	   T2."Aug" as c58,
	   T2."Sep" as c59,
	   T2."Okt" as c60,
	   T2."Nov" as c61,
	   T2."Dez" as c62,
	   CASE WHEN (T2."Kst" IN (3,4,5,6)) THEN ((CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Jan") ELSE (T1."Plan" / 12) END) + (CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Feb") ELSE (T1."Plan" / 12) END) + (CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Mrz") ELSE (T1."Plan" / 12) END) + (CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Apr") ELSE (T1."Plan" / 12) END) + (CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Mai") ELSE (T1."Plan" / 12) END) + (CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Jun") ELSE (T1."Plan" / 12) END) + (CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Jul") ELSE (T1."Plan" / 12) END) + (CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Aug") ELSE (T1."Plan" / 12) END) + (CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Sep") ELSE (T1."Plan" / 12) END) + (CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Okt") ELSE (T1."Plan" / 12) END) + (CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Nov") ELSE (T1."Plan" / 12) END) + (CASE WHEN (T1."KST" IN (3,4,5,6)) THEN (T1."Plan" * T2."Dez") ELSE (T1."Plan" / 12) END) - T1."Plan") ELSE null END as c63
from ("UE_EW_gesamt_2016" T1 left outer join "Saisonkurve" T2 on (T1."KST" = T2."Kst") and (T1."Jahr" = T2."Jahr"))
where (T1."Art" IS NULL)

END SQL
COLUMN,0,Art
COLUMN,1,Feld2
COLUMN,2,Acct Nr Ori
COLUMN,3,Ledger Accts Name
COLUMN,4,Marke 1
COLUMN,5,Modell
COLUMN,6,Abnehmergruppe
COLUMN,7,Ebene1
COLUMN,8,Ebene2_ori
COLUMN,9,Ebene3
COLUMN,10,Ebene4
COLUMN,11,Ebene5
COLUMN,12,Ebene6
COLUMN,13,Jahr
COLUMN,14,Mandant
COLUMN,15,Kürzel
COLUMN,16,Kst
COLUMN,17,Marke
COLUMN,18,Plan
COLUMN,19,Stück_ori
COLUMN,20,Stück
COLUMN,21,Wert1
COLUMN,22,Wert2
COLUMN,23,Wert3
COLUMN,24,Wert4
COLUMN,25,Wert5
COLUMN,26,Wert6
COLUMN,27,Wert7
COLUMN,28,Wert8
COLUMN,29,Wert9
COLUMN,30,Wert10
COLUMN,31,Wert11
COLUMN,32,Wert12
COLUMN,33,Stk1
COLUMN,34,Stk2
COLUMN,35,Stk3
COLUMN,36,Stk4
COLUMN,37,Stk5
COLUMN,38,Stk6
COLUMN,39,Stk7
COLUMN,40,Stk8
COLUMN,41,Stk9
COLUMN,42,Stk10
COLUMN,43,Stk11
COLUMN,44,Stk12
COLUMN,45,Ebene2
COLUMN,46,Kst_Saison
COLUMN,47,Summe
COLUMN,48,Id
COLUMN,49,Jahr_Saison
COLUMN,50,Jan
COLUMN,51,Feb
COLUMN,52,Mrz
COLUMN,53,Apr
COLUMN,54,Mai
COLUMN,55,Jun
COLUMN,56,Jul
COLUMN,57,Aug
COLUMN,58,Sep
COLUMN,59,Okt
COLUMN,60,Nov
COLUMN,61,Dez
COLUMN,62,Summe_1