C11/System/WERWISO/IQD/NW/NW_GW_BE.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,werwiso
DATASOURCENAME,C:\GlobalCube\System\WERWISO\IQD\nw\NW_GW_BE.imr
TITLE,NW_GW_BE.imr
BEGIN SQL
select T1."Filial_ID" as c1,
	   T1."LagerNummer" as c2,
	   T1."FzLagerNr" as c3,
	   T1."Hersteller" as c4,
	   T1."Typ" as c5,
	   T1."Farbe" as c6,
	   T1."Unfall" as c7,
	   T1."Baujahr" as c8,
	   T1."Erstzulassung" as c9,
	   T1."Modelljahr" as c10,
	   T1."HU" as c11,
	   T1."AU" as c12,
	   T1."Fahrgestellnummer" as c13,
	   T1."Kennzeichen" as c14,
	   T1."TaxiMietFS" as c15,
	   T1."Hubraum" as c16,
	   T1."PS" as c17,
	   T1."KW" as c18,
	   T1."Kilometerstand" as c19,
	   T1."EingangsDatum" as c20,
	   T1."NeuPreis" as c21,
	   T1."Einkaufspreis" as c22,
	   T1."EKMehrwertsteuerJaNein" as c23,
	   T1."PVKMehrwertsteuerJaNein" as c24,
	   T1."HVKMehrwertsteuerJaNein" as c25,
	   T1."PrivatRabattProzent" as c26,
	   T1."PrivatVerkaufspreis" as c27,
	   T1."PrivatMehrwertsteuer" as c28,
	   T1."HaendlerRabattProzent" as c29,
	   T1."HaendlerVerkaufspreis" as c30,
	   T1."HaendlerMehrwertsteuer" as c31,
	   T1."Handelsspanne" as c32,
	   T1."StandzeitTage" as c33,
	   T1."AuslagenZulassung" as c34,
	   T1."AuslagenHU" as c35,
	   T1."AuslagenAU" as c36,
	   T1."AuslagenGarantie" as c37,
	   T1."AuslagenWerkstatt" as c38,
	   T1."Anzahlung" as c39,
	   T1."Laufzeit" as c40,
	   T1."Rechnungsnummer" as c41,
	   T1."Reparaturkosten" as c42,
	   T1."Kundennummer" as c43,
	   T1."KundeAnrede" as c44,
	   T1."KundeVorname" as c45,
	   T1."KundeName1" as c46,
	   T1."KundeName2" as c47,
	   T1."KundeStrasse" as c48,
	   T1."KundePLZ" as c49,
	   T1."KundeOrt" as c50,
	   T1."Agenturfahrzeug" as c51,
	   T1."Fahrzeugart" as c52,
	   T1."Typzusatz" as c53,
	   T1."EinkaufRechnungsnummer" as c54,
	   T1."Inspektion" as c55,
	   T1."Gaspruefung" as c56,
	   T1."VerkaufspreisOhneRabatt" as c57,
	   T1."AbmeldeDatum" as c58,
	   T1."Erloeskonto" as c59,
	   T1."AnkaufvertragVom" as c60,
	   T1."VerkaufVertragVom" as c61,
	   T1."Bank" as c62,
	   T1."Restwert" as c63,
	   T1."Verkaufshilfe" as c64,
	   T1."Auftragsnummer" as c65,
	   T1."Bestelldatum" as c66,
	   T1."ProvisionDM" as c67,
	   T1."FinanzierungLeasing" as c68,
	   T1."Versicherung" as c69,
	   T1."Bonus" as c70,
	   T1."Fracht" as c71,
	   T1."Zulassung" as c72,
	   T1."Zubehoer" as c73,
	   T1."Garantie" as c74,
	   T1."LetzteBewertung" as c75,
	   T1."BewertungEk" as c76,
	   T1."BewertungVk" as c77,
	   T1."MotorArt" as c78,
	   T1."LetzteInspektion" as c79,
	   T1."LetzteInspektionKM" as c80,
	   T1."Polster" as c81,
	   T1."FarbCode" as c82,
	   T1."PolsterCode" as c83,
	   T1."HRabattPraemie" as c84,
	   T1."ZRabatt1Praemie" as c85,
	   T1."ZRabatt2Praemie" as c86,
	   T1."GebuehrenPraemie" as c87,
	   T1."Kraftstoffart_ID" as c88,
	   T1."Aufbau_ID" as c89,
	   T1."FGH_Nr" as c90,
	   T1."Verkaeufer" as c91,
	   T1."DivText1" as c92,
	   T1."DivText2" as c93,
	   T1."DivText3" as c94,
	   T1."DivText4" as c95,
	   T1."DivText5" as c96,
	   T1."FzArt" as c97,
	   T1."Wiederzulassung" as c98,
	   T1."Lack1" as c99,
	   T1."Lieferdatum" as c100,
	   T1."VerkaeuferNr" as c101,
	   T1."BestellMenge" as c102,
	   T1."Vorbesitzer" as c103,
	   T1."VehicleNumber" as c104,
	   T1."GMOrderNumber" as c105,
	   T1."OWBOrderId" as c106,
	   T1."OrderEntryDate" as c107,
	   T1."BusinessTransactionDate" as c108,
	   T1."BusinessTransactionTime" as c109,
	   T1."FleetIdentificationCode" as c110,
	   T1."CustomerLastName" as c111,
	   T1."CustomerOrderDate" as c112,
	   T1."OrderingStatus" as c113,
	   T1."OrderingStatusDate" as c114,
	   T1."SalesmanCode" as c115,
	   T1."TradeinYear" as c116,
	   T1."TradeinVIN" as c117,
	   T1."VerkaufVertragAm" as c118,
	   T1."Bruttokreditbetrag" as c119,
	   T1."Nettokreditbetrag" as c120,
	   T1."SollZinssatz" as c121,
	   T1."SollZinssatzArt" as c122,
	   T1."Kostentraeger" as c123,
	   T1."PraemienBonus" as c124,
	   T1."KdAuslieferung" as c125,
	   T1."KdAuslieferungGepl" as c126,
	   T1."NeupreisInklNova" as c127,
	   T1."Eigenverkauf" as c128,
	   T1."KRMMarkeBuchung" as c129,
	   T1."TechNr2" as c130,
	   T1."FzMarken_ID" as c131,
	   T1."FzMarkenKennz" as c132,
	   T1."FzKonzernmarke" as c133,
	   '1' as c134,
	   '1' as c135,
	   (substring(pack((ucase(T1."Hersteller"))) from 1 for POSITION(' ' IN pack((ucase(T1."Hersteller"))) || ' ') - 1)) as c136,
	   T1."Typ" as c137,
	   CASE WHEN (not ((substring(pack((ucase(T1."Hersteller"))) from 1 for POSITION(' ' IN pack((ucase(T1."Hersteller"))) || ' ') - 1))) IN ('DACIA')) THEN ((od_left((ucase(T1."Typ")),4))) ELSE (T1."Typ") END as c138,
	   CASE WHEN (T1."Fahrzeugart" IS NOT NULL) THEN ((truncate((asciiz(round(T1."LagerNummer",0,0),9)))) || ' / ' || (substring(T1."Fahrgestellnummer" from 12 for 7)) || ' - ' || (rtrim(T1."Fahrzeugart"))) ELSE ((truncate((asciiz(round(T1."LagerNummer",0,0),9)))) || ' / ' || (substring(T1."Fahrgestellnummer" from 12 for 7))) END as c139,
	   CASE WHEN ((T1."Fahrzeugart" IN ('Bestellt für Kunde  ','Bestellt für Lager  ','Neufahrzeug         ','Tageszulassung      ','Vorführwagen        ')) or (T1."Fahrzeugart" LIKE 'WEW%')) THEN ('Neuwagen') ELSE ('Gebrauchtwagen') END as c140,
	   (ucase((od_left(T1."Fahrzeugart",1)))) || (substring(T1."Fahrzeugart" from 2 for 20)) as c141,
	   '' as c142,
	   T2."Name_1" as c143,
	   CASE WHEN (T2."Name_1" IS NULL) THEN ((asciiz(round(T1."Filial_ID",0,0),2))) ELSE (T2."Name_1") END as c144,
	   CASE WHEN (T1."EingangsDatum" IS NOT NULL) THEN ((extract(DAY FROM (now()) - (cdatetime(T1."EingangsDatum"))))) ELSE (0) END as c145,
	   CASE WHEN ((CASE WHEN (T1."EingangsDatum" IS NOT NULL) THEN ((extract(DAY FROM (now()) - (cdatetime(T1."EingangsDatum"))))) ELSE (0) END) BETWEEN 0 AND 30) THEN ('0 - 30 Tage') WHEN ((CASE WHEN (T1."EingangsDatum" IS NOT NULL) THEN ((extract(DAY FROM (now()) - (cdatetime(T1."EingangsDatum"))))) ELSE (0) END) BETWEEN 31 AND 60) THEN ('31 - 60 Tage') WHEN ((CASE WHEN (T1."EingangsDatum" IS NOT NULL) THEN ((extract(DAY FROM (now()) - (cdatetime(T1."EingangsDatum"))))) ELSE (0) END) BETWEEN 61 AND 90) THEN ('61 - 90 Tage') WHEN ((CASE WHEN (T1."EingangsDatum" IS NOT NULL) THEN ((extract(DAY FROM (now()) - (cdatetime(T1."EingangsDatum"))))) ELSE (0) END) BETWEEN 91 AND 180) THEN ('91 - 180 Tage') WHEN ((CASE WHEN (T1."EingangsDatum" IS NOT NULL) THEN ((extract(DAY FROM (now()) - (cdatetime(T1."EingangsDatum"))))) ELSE (0) END) BETWEEN 181 AND 360) THEN ('181 - 360 Tage') WHEN ((CASE WHEN (T1."EingangsDatum" IS NOT NULL) THEN ((extract(DAY FROM (now()) - (cdatetime(T1."EingangsDatum"))))) ELSE (0) END) > 360) THEN ('> 360 Tage') ELSE null END as c146,
	   T1."Farbe" as c147,
	   T1."Vorbesitzer" as c148,
	   '' as c149,
	   '' as c150,
	   T1."Erstzulassung" as c151,
	   T1."Kilometerstand" as c152,
	   1 as c153,
	   T1."Einkaufspreis" as c154,
	   T1."PrivatVerkaufspreis" as c155,
	   (@CURRENT_DATE) as c156,
	   T1."Vorbesitzer" as c157,
	   CASE WHEN (T1."Fahrzeugart" IN ('Bestellt für Lager  ','Vorlauf GW diff.    ','Bestellt für Kunde  ')) THEN ('Vorlauf') ELSE ('Bestand') END as c158,
	   CASE WHEN (T1."VerkaufVertragVom" IS NOT NULL) THEN ('verkauft') ELSE ('Bestand') END as c159,
	   CASE WHEN (((substring(pack((ucase(T1."Hersteller"))) from 1 for POSITION(' ' IN pack((ucase(T1."Hersteller"))) || ' ') - 1))) IN ('DACIA','RENAULT','MG')) THEN (((substring(pack((ucase(T1."Hersteller"))) from 1 for POSITION(' ' IN pack((ucase(T1."Hersteller"))) || ' ') - 1)))) ELSE ('FREMD') END as c160,
	   'Freising' as c161
from ("\\172.17.171.208:6262\werwiso\WerWisoTabs\Mueller\Mandant.add"."Fahrzlag" T1 left outer join "\\172.17.171.208:6262\werwiso\WerWisoTabs\Mueller\Mandant.add"."Filialen" T2 on T1."Filial_ID" = T2."Filial_Id")
order by c2 asc

END SQL
COLUMN,0,Filial Id
COLUMN,1,Lagernummer
COLUMN,2,Fzlagernr
COLUMN,3,Hersteller
COLUMN,4,Typ
COLUMN,5,Farbe_ori
COLUMN,6,Unfall
COLUMN,7,Baujahr
COLUMN,8,Erstzulassung
COLUMN,9,Modelljahr
COLUMN,10,Hu
COLUMN,11,Au
COLUMN,12,Fahrgestellnummer
COLUMN,13,Kennzeichen
COLUMN,14,Taximietfs
COLUMN,15,Hubraum
COLUMN,16,Ps
COLUMN,17,Kw
COLUMN,18,Kilometerstand
COLUMN,19,Eingangsdatum
COLUMN,20,Neupreis
COLUMN,21,Einkaufspreis
COLUMN,22,Ekmehrwertsteuerjanein
COLUMN,23,Pvkmehrwertsteuerjanein
COLUMN,24,Hvkmehrwertsteuerjanein
COLUMN,25,Privatrabattprozent
COLUMN,26,Privatverkaufspreis
COLUMN,27,Privatmehrwertsteuer
COLUMN,28,Haendlerrabattprozent
COLUMN,29,Haendlerverkaufspreis
COLUMN,30,Haendlermehrwertsteuer
COLUMN,31,Handelsspanne
COLUMN,32,Standzeittage
COLUMN,33,Auslagenzulassung
COLUMN,34,Auslagenhu
COLUMN,35,Auslagenau
COLUMN,36,Auslagengarantie
COLUMN,37,Auslagenwerkstatt
COLUMN,38,Anzahlung
COLUMN,39,Laufzeit
COLUMN,40,Rechnungsnummer
COLUMN,41,Reparaturkosten
COLUMN,42,Kundennummer
COLUMN,43,Kundeanrede
COLUMN,44,Kundevorname
COLUMN,45,Kundename1
COLUMN,46,Kundename2
COLUMN,47,Kundestrasse
COLUMN,48,Kundeplz
COLUMN,49,Kundeort
COLUMN,50,Agenturfahrzeug
COLUMN,51,Fahrzeugart_ori
COLUMN,52,Typzusatz
COLUMN,53,Einkaufrechnungsnummer
COLUMN,54,Inspektion
COLUMN,55,Gaspruefung
COLUMN,56,Verkaufspreisohnerabatt
COLUMN,57,Abmeldedatum
COLUMN,58,Erloeskonto
COLUMN,59,Ankaufvertragvom
COLUMN,60,Verkaufvertragvom
COLUMN,61,Bank
COLUMN,62,Restwert
COLUMN,63,Verkaufshilfe
COLUMN,64,Auftragsnummer
COLUMN,65,Bestelldatum
COLUMN,66,Provisiondm
COLUMN,67,Finanzierungleasing
COLUMN,68,Versicherung
COLUMN,69,Bonus
COLUMN,70,Fracht
COLUMN,71,Zulassung
COLUMN,72,Zubehoer
COLUMN,73,Garantie
COLUMN,74,Letztebewertung
COLUMN,75,Bewertungek
COLUMN,76,Bewertungvk
COLUMN,77,Motorart
COLUMN,78,Letzteinspektion
COLUMN,79,Letzteinspektionkm
COLUMN,80,Polster
COLUMN,81,Farbcode
COLUMN,82,Polstercode
COLUMN,83,Hrabattpraemie
COLUMN,84,Zrabatt1praemie
COLUMN,85,Zrabatt2praemie
COLUMN,86,Gebuehrenpraemie
COLUMN,87,Kraftstoffart Id
COLUMN,88,Aufbau Id
COLUMN,89,Fgh Nr
COLUMN,90,Verkaeufer
COLUMN,91,Divtext1
COLUMN,92,Divtext2
COLUMN,93,Divtext3
COLUMN,94,Divtext4
COLUMN,95,Divtext5
COLUMN,96,Fzart
COLUMN,97,Wiederzulassung
COLUMN,98,Lack1
COLUMN,99,Lieferdatum
COLUMN,100,Verkaeufernr
COLUMN,101,Bestellmenge
COLUMN,102,Vorbesitzer
COLUMN,103,Vehiclenumber
COLUMN,104,Gmordernumber
COLUMN,105,Owborderid
COLUMN,106,Orderentrydate
COLUMN,107,Businesstransactiondate
COLUMN,108,Businesstransactiontime
COLUMN,109,Fleetidentificationcode
COLUMN,110,Customerlastname
COLUMN,111,Customerorderdate
COLUMN,112,Orderingstatus
COLUMN,113,Orderingstatusdate
COLUMN,114,Salesmancode
COLUMN,115,Tradeinyear
COLUMN,116,Tradeinvin
COLUMN,117,Verkaufvertragam
COLUMN,118,Bruttokreditbetrag
COLUMN,119,Nettokreditbetrag
COLUMN,120,Sollzinssatz
COLUMN,121,Sollzinssatzart
COLUMN,122,Kostentraeger
COLUMN,123,Praemienbonus
COLUMN,124,Kdauslieferung
COLUMN,125,Kdauslieferunggepl
COLUMN,126,Neupreisinklnova
COLUMN,127,Eigenverkauf
COLUMN,128,Krmmarkebuchung
COLUMN,129,Technr2
COLUMN,130,Fzmarken Id
COLUMN,131,Fzmarkenkennz
COLUMN,132,Fzkonzernmarke
COLUMN,133,Hauptbetrieb_ID
COLUMN,134,Standort_ID
COLUMN,135,Fabrikat_ori
COLUMN,136,Model
COLUMN,137,Modell_Beschreibung
COLUMN,138,FZG
COLUMN,139,Fahrzeugart
COLUMN,140,Fahrzeugtyp
COLUMN,141,Fahrzeugtyp_1
COLUMN,142,Name 1_Filialen
COLUMN,143,Standort_1
COLUMN,144,Standtage
COLUMN,145,Standtagestaffel
COLUMN,146,Farbe
COLUMN,147,Name_Lieferant
COLUMN,148,Name_Einkäufer
COLUMN,149,FZG_Einkäufer
COLUMN,150,EZ
COLUMN,151,Km-Stand
COLUMN,152,Menge
COLUMN,153,Einsatz
COLUMN,154,geplanter_VK
COLUMN,155,Invoice Date
COLUMN,156,Lieferant/Einkäufer
COLUMN,157,Bestand/Vorlauf
COLUMN,158,Bestand / verk. Bestand
COLUMN,159,Fabrikat
COLUMN,160,Standort_Name