From what I remember from your query, it was working ok if you made the corrections I told you.
If you want to obtain the total devaluation value, you should not include columns from Vehicle_Depreciations table.
This might help you, read the comments I made
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
platenrchar(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