COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,D:\Gaps\Portal\System\IQD\NW\NW_BE.imr
TITLE,NW_BE.imr
BEGIN SQL
select T1."UNIT_NUMBER" as c1,
	   T1."BASIS_NUMBER" as c2,
	   T1."ECC_STATUS" as c3,
	   T1."VEHICLE_TYPE" as c4,
	   T1."BOOK_KEEPING_CODE" as c5,
	   T1."MILEAGE" as c6,
	   T1."PURCH_DEPARTMENT" as c7,
	   T1."PURCH_SALSMAN_CODE" as c8,
	   T1."PURCH_INVNO" as c9,
	   T1."PURCH_DATE" as c10,
	   T1."BUD_SALE_DET" as c11,
	   T1."BUD_PURCH_PRICE" as c12,
	   T1."BUD_REG_FEE" as c13,
	   T1."BUD_DEDUCT_FEE" as c14,
	   T1."BUD_COST" as c15,
	   T1."PREV_OWNER" as c16,
	   T1."PURCH_PRICE_UNIT" as c17,
	   T1."COST_UNIT" as c18,
	   T1."SALES_PRICE_UNIT" as c19,
	   T1."STOCK_DAYS" as c20,
	   T1."LOCATION_CODE" as c21,
	   T1."CONTRACT_DATE" as c22,
	   T1."RECEPTION_DATE" as c23,
	   T2."REGISTER_NUMBER" as c24,
	   T2."CHASSIS_NUMBER" as c25,
	   T2."CAR_GROUP" as c26,
	   T2."CAR_STATUS" as c27,
	   T2."FIRST_REG_DATE" as c28,
	   T2."ARRIVAL_DATE" as c29,
	   T2."MODEL_TEXT" as c30,
	   T2."COLOUR_CF" as c31,
	   T2."MODEL_LINE" as c32,
	   T2."WORKSHOP_MODEL" as c33,
	   T2."COLOUR_CODE" as c34,
	   T2."MAKE" as c35,
	   T2."MAKE_CD" as c36,
	   T3."MODEL_LINE" as c37,
	   T3."MOD_LIN_SPECIFY" as c38,
	   T3."MAKE_CD" as c39,
	   CASE WHEN (T1."ECC_STATUS" = '21') THEN ('21 - Vorlauf') WHEN (T1."ECC_STATUS" = '25') THEN ('25 - für Kd best. FZG') WHEN (T1."ECC_STATUS" = '41') THEN ('41 - Bestand') WHEN (T1."ECC_STATUS" = '44') THEN ('44 - am Hof nicht fakt.') WHEN (T1."ECC_STATUS" = '64') THEN ('64 - verkauft') ELSE null END as c40,
	   '1' as c41,
	   '01' as c42,
	   CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','M','B','P','Q')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') WHEN (T1."VEHICLE_TYPE" IN ('G')) THEN ('Geschäftsfahrzeug') ELSE null END as c43,
	   T2."MAKE_CD" as c44,
	   T3."MOD_LIN_SPECIFY" as c45,
	   T2."MODEL_TEXT" as c46,
	   T2."CHASSIS_NUMBER" as c47,
	   T2."COLOUR_CODE" || '-' || T2."COLOUR_CF" as c48,
	   T1."UNIT_NUMBER" || T2."CHASSIS_NUMBER" as c49,
	   CASE WHEN (T1."VEHICLE_TYPE" IN ('N','P','Q')) THEN ('N - Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('V','W','X')) THEN ('V - Vorführwagen') WHEN (T1."VEHICLE_TYPE" IN ('M','Y')) THEN ('M - Mietwagen/Opel Rent') WHEN (T1."VEHICLE_TYPE" IN ('R')) THEN ('R - GW regelbesteuert') WHEN (T1."VEHICLE_TYPE" IN ('D')) THEN ('D - GW §25A UStG') WHEN (T1."VEHICLE_TYPE" IN ('G')) THEN ('G - Geschäftsfahrzeug') ELSE null END as c50,
	   (now()) as c51,
	   CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((extract(DAY FROM ((now())) - T1."PURCH_DATE"))) ELSE ((extract(DAY FROM ((now())) - T1."RECEPTION_DATE"))) END as c52,
	   CASE WHEN ((CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((extract(DAY FROM ((now())) - T1."PURCH_DATE"))) ELSE ((extract(DAY FROM ((now())) - T1."RECEPTION_DATE"))) END) BETWEEN 0 AND 30) THEN ('0 - 30 Tage') WHEN ((CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((extract(DAY FROM ((now())) - T1."PURCH_DATE"))) ELSE ((extract(DAY FROM ((now())) - T1."RECEPTION_DATE"))) END) BETWEEN 31 AND 60) THEN ('31 - 60 Tage') WHEN ((CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((extract(DAY FROM ((now())) - T1."PURCH_DATE"))) ELSE ((extract(DAY FROM ((now())) - T1."RECEPTION_DATE"))) END) BETWEEN 61 AND 90) THEN ('61 - 90 Tage') WHEN ((CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((extract(DAY FROM ((now())) - T1."PURCH_DATE"))) ELSE ((extract(DAY FROM ((now())) - T1."RECEPTION_DATE"))) END) BETWEEN 91 AND 180) THEN ('91 - 180 Tage') WHEN ((CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((extract(DAY FROM ((now())) - T1."PURCH_DATE"))) ELSE ((extract(DAY FROM ((now())) - T1."RECEPTION_DATE"))) END) > 180) THEN ('> 180 Tage') ELSE null END as c53,
	   T1."PURCH_PRICE_UNIT" + T1."COST_UNIT" as c54,
	   1 as c55,
	   '' as c56,
	   T2."MOTOR_CODE" as c57,
	   'Uslar' as c58,
	   T1."COST_UNIT" as c59,
	   T1."BUD_COST" as c60,
	   T1."BUD_SALE_DET" as c61
from "deop01"."dbo"."UNIT_FILE" T1,
	"deop01"."dbo"."VEHICLE_v" T2,
	"deop01"."dbo"."vPP5Q" T3
where (T1."BASIS_NUMBER" = T2."BASIS_NUMBER") and (T2."MODEL_LINE" = T3."MODEL_LINE")
and (T1."ECC_STATUS" IN ('41','44'))
order by c52 desc

END SQL
COLUMN,0,Unit Number
COLUMN,1,Basis Number
COLUMN,2,Ecc Status
COLUMN,3,Vehicle Type
COLUMN,4,Book Keeping Code
COLUMN,5,Mileage
COLUMN,6,Purch Department
COLUMN,7,Purch Salsman Code
COLUMN,8,Purch Invno
COLUMN,9,Purch Date
COLUMN,10,Bud Sale Det
COLUMN,11,Bud Purch Price
COLUMN,12,Bud Reg Fee
COLUMN,13,Bud Deduct Fee
COLUMN,14,Bud Cost
COLUMN,15,Prev Owner
COLUMN,16,Purch Price Unit
COLUMN,17,Cost Unit
COLUMN,18,Sales Price Unit
COLUMN,19,Stock Days
COLUMN,20,Location Code
COLUMN,21,Contract Date
COLUMN,22,Reception Date
COLUMN,23,Register Number
COLUMN,24,Chassis Number
COLUMN,25,Car Group
COLUMN,26,Car Status
COLUMN,27,First Reg Date
COLUMN,28,Arrival Date
COLUMN,29,Model Text
COLUMN,30,Colour Cf
COLUMN,31,Model Line
COLUMN,32,Workshop Model
COLUMN,33,Colour Code
COLUMN,34,Make
COLUMN,35,Make Cd
COLUMN,36,Model Line
COLUMN,37,Mod Lin Specify
COLUMN,38,Make Cd
COLUMN,39,ECC_Status_Text
COLUMN,40,Hauptbetrieb
COLUMN,41,Standort
COLUMN,42,Fahrzeugart
COLUMN,43,Fabrikat
COLUMN,44,Model
COLUMN,45,Modellbez
COLUMN,46,Fahrgestellnr
COLUMN,47,Farbe
COLUMN,48,FZG
COLUMN,49,Fahrzeugtyp
COLUMN,50,Heute
COLUMN,51,Standtage
COLUMN,52,Standtagestaffel
COLUMN,53,Einsatz
COLUMN,54,Menge
COLUMN,55,Fahrzeugart_Konsi
COLUMN,56,Motor Code
COLUMN,57,Betrieb
COLUMN,58,tatsächl. Kosten
COLUMN,59,geplante Kosten
COLUMN,60,geplanter VK