CELKO (8/17/2012)

---

Aren't you required by law to use the VIN and not invent your own identifier? Or is this just aq skeleton? I have a horror story about a taxi company, VINs, medallions, and tax id numbers …,

>> Basically the two tables in use are Vehicle and Vehicle_Depreciation. There is one row per VIN in the vehicle table, within the Vehicle_Depreciation table there is two rows per VIN. These two rows have different monthly depreciation values based on different date periods. <<

They are attributes of the same Vehicle, so why are they split ac cross two rows? This sounds like an example of the design flaw known as attribute splitting. Let's look closer.

The trouble I am having is calculating the depreciation period and depreciation value for cars that have been blocked for sale. The goal is to depreciate the vehicle up until and including the month that the vehicle is blocked(placed) for sale.

What I would like to achieve in this statement is

-- when vehicle is blocked for sale

-- then calculate depreciation up until blocked for sale date(this could be a combination of both depreciation dates eg:48 months on first band at 112.47 and 5 months on second band at 508.61 combine these two values and subtract from the purchase value of the vehicle(M.value)

I have tried using subqueries to no avail, got errors because case statement was trying to return more than one value. I have provided some sample table structure and some results from the query. I would appreciate any input as I am working to develop my SQL skills.

You are a bit behind on the syntax and are writing in release 6.0 SQL Server. We have a lot of ANSI Standard stuff now. We need keys and constraints, but you gave us nothing. Tables have collective names if possible, but never singular ones. Time periods are done with (start_date, end_date) pairs; The correct term is INTERVAL, not block and T-SQL does nto support them yet.

VALUE is reserved word in ANSI and too vague to use. Dates have to be in quoted strings. Blue Book? CarMax? Whose value?

CREATE TABLE Motorpool

(vin CHAR(19) NOT NULL PRIMARY KEY

CHECK (vin LIKE <<regular expression from hell>>),

vehicle_value DECIMAL (8,2) NOT NULL,

CHECK (vehicle_value >= 0.00),

vehicle_purchase_date DATE NOT NULL,

vehicle_retirement_date DATE, --- null is in use

CHECK (vehicle_purchase_date < vehicle_retirement_date, fleet_type ??,

something_status ??,

etc);

Too bad you did not tell us all of the columns you later use in the query. See the collective name, industry standard identifier, correct data type for currency, and proper temporal model? Take a tip from an old SQL guru; 80-95% of the work is done in the DDL, not in the DML.

Your table had no keys, not way to enforce the two deprecation rule. New columns magically appear in that query, the names are too vague or are just wrong (brand_id is vehicle_make in this industry, likewise vehicle_model, etc. Category of what?

The used of CASE is wrong; you are trying to wrote if-then-else logic and SQL. Use the idiom “some_date BETWEEN start_date AND COALESCE (end_daet, CURRENT_TIMESTAMP) instead.

Try this clean up:

CREATE TABLE Vehicle_Depreciation_Schedule

( vin CHAR(19) NOT NULL PRIMARY KEY

REFERENCES Motorpool),

depre_start_date DATE NOT NULL,

PRIMARY KEY (vin, depre_start_date),

depre_end_date DATE NOT NULL,

CHECK (depre_start_date < depre_end_date),

vehicle_depreciation_value DECIMAL (8,2) NOT NULL

CHECK (vehicle_depreciation_value >= 0.00));

after this point, your code is so confused I cannot read it (I just had eye surgery). Play with this and post again. I get my right eye done on Monday, so I might not get back to you immediately.

My thought was to download the KBB tables and search on (mileage, make, model, year).