--The problem with your code Luiz is that it is calculating the two periods together, but the date_block (20120406) is between the second period, so I need a way of only calculating up until the date_block (20120406) . The figure of depr I need from the below sql is 3925.58, this figure is made up of 24 months at 151..45 and 2 months at 145.39 . It seems the query is not taking into account the Date_block date.
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 29, 8057.85, '20120406', 'of', '08D12345'
INSERT @Vehicle_Depreciations
SELECT '20100301', '20120229' ,151.45,29 UNION ALL
SELECT '20120301', '20140801' ,145.39, 29
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