evaluating and combining values from two rows

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).

;WITH Tally (n) AS (
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns)
SELECT a.unitnr, depr_date=m, Vehicle_Depreciation_Value --=SUM(Vehicle_Depreciation_Value)
--SELECT a.unitnr, Vehicle_Depreciation_Value=SUM(Vehicle_Depreciation_Value)
FROM @Vehicle a
INNER JOIN @Vehicle_Depreciations b
ON a.unitnr = b.unitnr
CROSS APPLY (
SELECT n, m=DATEADD(month, n-1, Start_Date)
FROM Tally
WHERE n BETWEEN 1 AND 1+DATEDIFF(month, Start_Date, End_Date)) c
WHERE m <= ISNULL(date_block, CAST('2099-01-01' AS DATETIME))
--GROUP BY a.unitnr
ORDER BY a.unitnr, m

Declare @date as datetime
set @date ='20120731'
DECLARE @vehicle TABLE (
unitnr int,
value decimal( 18, 8), --changed the data type
date_block datetime, --changed the data type
type_fleet char(2), --added column
platenr char(9)) --added column
DECLARE @Vehicle_Depreciations TABLE (
Start_Date datetime,
End_Date datetime,
Vehicle_Depreciation_Value decimal( 18, 8), --changed the data type
unitnr int)

--Changed ALL dates format
INSERT @Vehicle
SELECT 724, 9430, '20120723', 'of', '08D12345' UNION ALL
SELECT 725, 12430, null, 'of', '23s546321'

INSERT @Vehicle_Depreciations
SELECT '20090506', '20120229' ,175,724 UNION ALL
SELECT '20120301', '20120830' ,284, 724 UNION ALL
SELECT '20100430', '20120229',159,725 UNION ALL
SELECT '20120301', '20121231',183,725

select 'For Sale' as Type,
v.unitnr,
v.value,
v.platenr,
v.Type_Fleet,
--vd.Start_Date, --If you uncomment this fields, you'll get a detail for each depreciation period
--vd.End_Date,
v.Date_Block ,
--vd.Vehicle_Depreciation_Value,
sum(CASE WHEN vd.END_DATE >@DATE--checks if end_date is greater than the date passed
THEN DATEDIFF(MONTH,vd.START_DATE,DATEADD(day,1,@DATE))--determines the months of depreciation from start_date till date passed
WHEN vd.END_DATE > v.Date_Block-- checks if end_date is past Date_Blocked for sale
THEN DATEDIFF(MONTH,vd.START_DATE,v.Date_Block) --if so, then determines the months of depreciation from start_Date till dat_blocked
ELSE DATEDIFF(MONTH,vd.START_DATE,vd.END_DATE+1) END * vd.Vehicle_Depreciation_Value )AS depr --determines the months of depreciation between start_date and end_date
from @vehicle v inner join @Vehicle_Depreciations vd on v.unitnr = vd.UnitNr
WHERE v.Date_Block is not null
group by v.unitnr, v.value, v.Date_Block,v.platenr, v.Type_Fleet