C11/System/AUTOLINE/IQD/NW/nw_gw_eds_bilanz_bestand_fibu.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_CARLO
DATASOURCENAME,C:\GlobalCube\System\IQD\nw\nw_gw_eds_bilanz_bestand_fibu.imr
TITLE,nw_gw_eds_bilanz_bestand_fibu.imr
BEGIN SQL
select c175 as c1,
	   c174 as c2,
	   c173 as c3,
	   c172 as c4,
	   c171 as c5,
	   c170 as c6,
	   c169 as c7,
	   c125 as c8,
	   c168 as c9,
	   c167 as c10,
	   c166 as c11,
	   c165 as c12,
	   c164 as c13,
	   c163 as c14,
	   c162 as c15,
	   c161 as c16,
	   c121 as c17,
	   c160 as c18,
	   c159 as c19,
	   c158 as c20,
	   c157 as c21,
	   c156 as c22,
	   c155 as c23,
	   c154 as c24,
	   c153 as c25,
	   c152 as c26,
	   c151 as c27,
	   c150 as c28,
	   c149 as c29,
	   c148 as c30,
	   c147 as c31,
	   c146 as c32,
	   c145 as c33,
	   c144 as c34,
	   c99 as c35,
	   c143 as c36,
	   c142 as c37,
	   c141 as c38,
	   c140 as c39,
	   c139 as c40,
	   c138 as c41,
	   c137 as c42,
	   c136 as c43,
	   c134 as c44,
	   c135 as c45,
	   '1' as c46,
	   c104 as c47,
	   c134 as c48,
	   c133 as c49,
	   c132 as c50,
	   c131 as c51,
	   c130 as c52,
	   c129 as c53,
	   c127 as c54,
	   c128 as c55,
	   c127 as c56,
	   c126 as c57,
	   c125 as c58,
	   c124 as c59,
	   c123 as c60,
	   c122 as c61,
	   c121 as c62,
	   c103 as c63,
	   c120 as c64,
	   c119 as c65,
	   c118 as c66,
	   c117 as c67,
	   c116 as c68,
	   c113 as c69,
	   c100 as c70,
	   c115 as c71,
	   c114 as c72,
	   c113 as c73,
	   c112 as c74,
	   c111 as c75,
	   c110 as c76,
	   c109 as c77,
	   c108 as c78,
	   c107 as c79,
	   c106 as c80,
	   c105 as c81,
	   c104 as c82,
	   c104 as c83,
	   c103 as c84,
	   XSUM(c124 for c99) as c85,
	   c102 as c86,
	   '1' as c87,
	   '00' as c88,
	   'Bestand Stück' as c89,
	   c101 as c90,
	   c100 as c91,
	   1 as c92,
	   XCOUNT(c175 for c99) as c93,
	   1 / (XCOUNT(c175 for c99)) as c94
from 
(select T2."Fahrgestellnummer" as c99,
	   (CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",4)) IN ('RENT')) THEN ('Mietwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWD')) THEN ('GW diffb.') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWR')) THEN ('GW regelb.') ELSE ('Neuwagen') END) as c100,
	   CASE WHEN ((CASE WHEN ((CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",4)) IN ('RENT')) THEN ('Mietwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWD')) THEN ('GW diffb.') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWR')) THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('GW regelb.','GW diffb.')) THEN ('Gebrauchtwagen') WHEN ((CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",4)) IN ('RENT')) THEN ('Mietwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWD')) THEN ('GW diffb.') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWR')) THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('Vorführwagen','Neuwagen','Mietwagen')) THEN ('Neuwagen') ELSE null END) = 'Neuwagen') THEN ('Neuw. Bestand') WHEN ((CASE WHEN ((CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",4)) IN ('RENT')) THEN ('Mietwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWD')) THEN ('GW diffb.') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWR')) THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('GW regelb.','GW diffb.')) THEN ('Gebrauchtwagen') WHEN ((CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",4)) IN ('RENT')) THEN ('Mietwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWD')) THEN ('GW diffb.') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWR')) THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('Vorführwagen','Neuwagen','Mietwagen')) THEN ('Neuwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('Gebrauchtw. Bestand') ELSE null END as c101,
	   ((now())) - INTERVAL '001 00:00:00.000' as c102,
	   T5."Markencode" as c103,
	   T5."Lagerortcode" as c104,
	   T6."Ausstattungscode" || ' - ' || T6."Beschreibung" as c105,
	   T6."Beschreibung" as c106,
	   T6."Ausstattungskennzeichen" as c107,
	   T6."Ausstattungscode" as c108,
	   CASE WHEN (((extract(DAY FROM ((now())) - T5."Einkaufslieferdatum"))) BETWEEN 0 AND 30) THEN ('0 - 30 Tage') WHEN (((extract(DAY FROM ((now())) - T5."Einkaufslieferdatum"))) BETWEEN 31 AND 60) THEN ('31 - 60 Tage') WHEN (((extract(DAY FROM ((now())) - T5."Einkaufslieferdatum"))) BETWEEN 61 AND 90) THEN ('61 - 90 Tage') WHEN (((extract(DAY FROM ((now())) - T5."Einkaufslieferdatum"))) BETWEEN 91 AND 180) THEN ('91 - 180 Tage') WHEN (((extract(DAY FROM ((now())) - T5."Einkaufslieferdatum"))) > 180) THEN ('> 180 Tage') ELSE null END as c109,
	   (extract(DAY FROM ((now())) - T5."Einkaufslieferdatum")) as c110,
	   (now()) as c111,
	   T5."Einkaufslieferdatum" as c112,
	   T5."Produktbuchungsgruppe" as c113,
	   T5."Fahrgestellnummer" as c114,
	   T2."Fahrgestellnummer" || ' - ' || T5."Produktbuchungsgruppe" as c115,
	   T5."Verkaufsdatum" as c116,
	   T1."Nr_" || ' - ' || T1."Name" as c117,
	   CASE WHEN ((CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",4)) IN ('RENT')) THEN ('Mietwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWD')) THEN ('GW diffb.') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWR')) THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('GW regelb.','GW diffb.')) THEN ('Gebrauchtwagen') WHEN ((CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",4)) IN ('RENT')) THEN ('Mietwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWD')) THEN ('GW diffb.') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWR')) THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('Vorführwagen','Neuwagen','Mietwagen')) THEN ('Neuwagen') ELSE null END as c118,
	   T5."Modell" as c119,
	   T5."Fahrzeugstatus" as c120,
	   T2."Benutzer ID" as c121,
	   CASE WHEN (T2."Filialcode" IN ('00','01')) THEN ('00') ELSE (T2."Filialcode") END as c122,
	   CASE WHEN (((od_left(T2."Sachkontonr_",2)) IN ('80','81','82')) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((cast_float(T2."Menge"))) * -1) WHEN (((od_left(T2."Sachkontonr_",1)) = '8') and (not (od_left(T2."Sachkontonr_",2)) IN ('80','81','82'))) THEN (((cast_float(T2."Menge"))) * -1) ELSE (0) END as c123,
	   CASE WHEN (((od_left(T2."Sachkontonr_",1)) = '8') or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((cast_float(T2."Betrag"))) * -1) ELSE (((cast_float(T2."Betrag")))) END as c124,
	   T2."Sachkontonr_" as c125,
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) || ' - ' || T3."Name" as c126,
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) as c127,
	   CASE WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) LIKE '1%') THEN ('1') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) LIKE '2%') THEN ('2') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) LIKE '3%') THEN ('6') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) = '41') THEN ('3') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) = '44') THEN ('4') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) = '45') THEN ('5') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) LIKE '5%') THEN ('7') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) LIKE '9%') THEN ('0') ELSE null END as c128,
	   T2."Belegnr_" || ' - ' || T2."Beschreibung" || ' - ' || T2."Benutzer ID" as c129,
	   CASE WHEN (((T5."Verkaufsdatum" = TIMESTAMP '1753-01-01 00:00:00.000') or (T5."Verkaufsdatum" IS NULL)) or (T5."Verkaufsdatum" >= TIMESTAMP '2010-01-01 00:00:00.000')) THEN (T2."Buchungsdatum") ELSE (T5."Verkaufsdatum") END as c130,
	   (cast_float(T2."Menge")) as c131,
	   (database()) as c132,
	   CASE WHEN (T2."Markencode" = '') THEN ('fehlt') ELSE (T2."Markencode") END as c133,
	   T4."Bezeichnung" as c134,
	   (cast_float(T2."Betrag")) as c135,
	   T4."Code" as c136,
	   T3."Name" as c137,
	   T3."Code" as c138,
	   T2."Fahrzeugklassecode" as c139,
	   T2."Storniert" as c140,
	   T2."Umgebucht" as c141,
	   T2."Fahrzeug-Kz" as c142,
	   T2."Buchnummer" as c143,
	   T2."Hauptbereich" as c144,
	   T2."Filialcode" as c145,
	   T2."Herkunftsnr_" as c146,
	   T2."Herkunftsart" as c147,
	   T2."Externe Belegnummer" as c148,
	   T2."Belegdatum" as c149,
	   T2."Habenbetrag" as c150,
	   T2."Sollbetrag" as c151,
	   T2."Transaktionsnr_" as c152,
	   T2."Gegenkontoart" as c153,
	   T2."Produktbuchungsgruppe" as c154,
	   T2."Geschäftsbuchungsgruppe" as c155,
	   T2."Buchungsart" as c156,
	   T2."MWSt Betrag" as c157,
	   T2."Menge" as c158,
	   T2."Nachbuchung" as c159,
	   T2."Herkunftscode" as c160,
	   T2."Markencode" as c161,
	   T2."Kostenstellencode" as c162,
	   T2."Betrag" as c163,
	   T2."Gegenkontonr_" as c164,
	   T2."Beschreibung" as c165,
	   T2."Belegnr_" as c166,
	   T2."Belegart" as c167,
	   T2."Buchungsdatum" as c168,
	   T2."Lfd_ Nr_" as c169,
	   T1."Kostenstellen Buchung" as c170,
	   T1."Soll_Haben" as c171,
	   T1."GuV_Bilanz" as c172,
	   T1."Kontoart" as c173,
	   T1."Name" as c174,
	   T1."Nr_" as c175
from "CARLO"."import"."Sachkonto" T1,
	(("CARLO"."import"."Sachposten" T2 
	left outer join "CARLO"."import"."Kostenstelle" T3 on T3."Code" = T2."Kostenstellencode") 
	left outer join "CARLO"."import"."Filialbezeichnung" T4 on T2."Filialcode" = T4."Code"),
	("CARLO"."import"."Fahrzeug" T5 
	left outer join "CARLO"."import"."Fahrzeug_Ausstattung" T6 on (T5."Fahrgestellnummer" = T6."Fahrgestellnummer") and (T6."Ausstattungskennzeichen" = 1))
where (T1."Nr_" = T2."Sachkontonr_") and (T2."Fahrgestellnummer" = T5."Fahrgestellnummer")
and ((T1."GuV_Bilanz" = 1) and (((((((((T1."Nr_" BETWEEN '31000' AND '32400') and (not T1."Nr_" IN ('31700','31650','31999'))) or (T1."Nr_" BETWEEN '38000' AND '38010')) or (T1."Nr_" IN ('32900','32950'))) and (T2."Fahrgestellnummer" <> ' ')) and (T5."Fahrzeugstatus" IN (0,1,2,6))) and ((T5."Verkaufsdatum" IN (TIMESTAMP '1753-01-01 00:00:00.000')) or (T5."Verkaufsdatum" IS NULL))) and (T2."Buchnummer" = T5."Aktuelle Buchnummer")) or ((((((T1."Nr_" IN ('15100','15200')) and ((CASE WHEN (T5."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",4)) IN ('RENT')) THEN ('Mietwagen') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWD')) THEN ('GW diffb.') WHEN ((od_left(T5."Produktbuchungsgruppe",3)) IN ('GWR')) THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('Vorführwagen','Neuwagen'))) and (T2."Fahrgestellnummer" <> ' ')) and (T5."Fahrzeugstatus" IN (0,1,2,6))) and ((T5."Verkaufsdatum" IN (TIMESTAMP '1753-01-01 00:00:00.000')) or (T5."Verkaufsdatum" IS NULL))) and (((CASE WHEN (((od_left(T2."Sachkontonr_",1)) = '8') or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((cast_float(T2."Betrag"))) * -1) ELSE (((cast_float(T2."Betrag")))) END) >= 240) or ((CASE WHEN (((od_left(T2."Sachkontonr_",1)) = '8') or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((cast_float(T2."Betrag"))) * -1) ELSE (((cast_float(T2."Betrag")))) END) <= -240)))))
order by c99 asc
) D1

END SQL
COLUMN,0,Nr
COLUMN,1,Name
COLUMN,2,Kontoart
COLUMN,3,Guv Bilanz
COLUMN,4,Soll Haben
COLUMN,5,Kostenstellen Buchung
COLUMN,6,Lfd Nr
COLUMN,7,Sachkontonr
COLUMN,8,Buchungsdatum
COLUMN,9,Belegart
COLUMN,10,Belegnr
COLUMN,11,Beschreibung
COLUMN,12,Gegenkontonr
COLUMN,13,Betrag_ori
COLUMN,14,Kostenstellencode
COLUMN,15,Markencode
COLUMN,16,Benutzer Id
COLUMN,17,Herkunftscode
COLUMN,18,Nachbuchung
COLUMN,19,Menge_ori
COLUMN,20,Mwst Betrag
COLUMN,21,Buchungsart
COLUMN,22,Geschäftsbuchungsgruppe
COLUMN,23,Produktbuchungsgruppe_FIBU
COLUMN,24,Gegenkontoart
COLUMN,25,Transaktionsnr
COLUMN,26,Sollbetrag
COLUMN,27,Habenbetrag
COLUMN,28,Belegdatum
COLUMN,29,Externe Belegnummer
COLUMN,30,Herkunftsart
COLUMN,31,Herkunftsnr
COLUMN,32,Filialcode
COLUMN,33,Hauptbereich
COLUMN,34,Fahrgestellnummer
COLUMN,35,Buchnummer
COLUMN,36,Fahrzeug-kz
COLUMN,37,Umgebucht
COLUMN,38,Storniert
COLUMN,39,Fahrzeugklassecode
COLUMN,40,Code
COLUMN,41,Name
COLUMN,42,Code
COLUMN,43,Bezeichnung
COLUMN,44,Betrag_1
COLUMN,45,Hauptbetrieb
COLUMN,46,Standort
COLUMN,47,Betrieb_1
COLUMN,48,Marke_ori
COLUMN,49,Mandant
COLUMN,50,Menge_1
COLUMN,51,Bookkeep Date_ori
COLUMN,52,Text
COLUMN,53,KST_aus_Code
COLUMN,54,Kostenstelle_ori
COLUMN,55,KST_1
COLUMN,56,KST_2
COLUMN,57,Acct Nr
COLUMN,58,Betrag
COLUMN,59,Menge_ori
COLUMN,60,Betrieb
COLUMN,61,Benutzer
COLUMN,62,Fabrikat
COLUMN,63,Fahrzeugstatus
COLUMN,64,Model
COLUMN,65,Fahrzeugart
COLUMN,66,Konto
COLUMN,67,Verkaufsdatum
COLUMN,68,Produktbuchungsgruppe
COLUMN,69,Fahrzeugtyp
COLUMN,70,FZG
COLUMN,71,Fahrgestellnummer_FZG
COLUMN,72,Fahrzeugtyp_1
COLUMN,73,Einkaufslieferdatum_FZG
COLUMN,74,Heute
COLUMN,75,Standtage
COLUMN,76,Standtagestaffel
COLUMN,77,Ausstattungscode
COLUMN,78,Ausstattungskennzeichen
COLUMN,79,Beschreibung_Ausstattung
COLUMN,80,Farbe
COLUMN,81,Standort_1
COLUMN,82,Lagerortcode
COLUMN,83,Markencode_FZG
COLUMN,84,Betrag_gesamt
COLUMN,85,Bookkeep Date
COLUMN,86,Rechtseinheit
COLUMN,87,Betrieb
COLUMN,88,Konto_1
COLUMN,89,Ebene1
COLUMN,90,Ebene2
COLUMN,91,Stück_Basis
COLUMN,92,Stück_Berechnung
COLUMN,93,Stück