July 26, 2012 at 10:20 am
Ok Here is my code and a sample of my result set. Basically I am working with the computed column BlockedNBV which is basically the net book value of a car which has been marked for sale. I need to calculate the depreciation for this vehicle from a start date to the date which it was placed for sale which is represented here by the column Date_block.the problem i am having is that it is returning a crazy value eg 1641677.27 where i would be hoping to see a figure which calculated the number of months between when the vehicle was bought to when it was placed for sale eg 24 and then multiply that number by the monthly depreciation value to get the total depreciation and then subtract that figure from the vehicle.value which was cost price. I am trying to do this in the second case statement below. Any help is welcome , thanks in advance.
Select Vehicle.unitnr, Vehicle.platenr, Vehicle.groupid, Vehicle.brandId, Vehicle.modelId, Vehicle.category, Vehicle.kms, Vehicle.value, Vehicle.Type_Fleet,
Vehicle.Date_Block,
(select SUM(
(CASE WHEN Vehicle_Depreciations.END_DATE >GETDATE()
THEN DATEDIFF(month,Vehicle_Depreciations.START_DATE,GETDATE())
ELSE DATEDIFF(month,Vehicle_Depreciations.START_DATE,Vehicle_Depreciations.END_DATE) END * Vehicle_Depreciations.Vehicle_Depreciation_Value))
FROM Vehicle_Depreciations where
Vehicle.unitnr = Vehicle_Depreciations.UnitNr) as Depr,
Vehicle.value - (select sum(
(CASE WHEN Vehicle.Date_Block IS not null and Vehicle.Date_Block > Vehicle_Depreciations.START_DATE
THEN DATEDIFF(month,Vehicle_Depreciations.START_DATE,Vehicle.Date_Block)
ELSE 0 END * Vehicle_Depreciations.Vehicle_Depreciation_Value))
FROM Vehicle,Vehicle_Depreciations
where Vehicle.unitnr = Vehicle_Depreciations.UnitNr ) as BlockedNBV,
Vehicle.value - (select SUM(
(CASE WHEN Vehicle_Depreciations.END_DATE >GETDATE()
THEN DATEDIFF(month,Vehicle_Depreciations.START_DATE,GETDATE())
ELSE DATEDIFF(month,Vehicle_Depreciations.START_DATE,Vehicle_Depreciations.END_DATE) END * Vehicle_Depreciations.Vehicle_Depreciation_Value))
FROM Vehicle_Depreciations where
Vehicle.unitnr = Vehicle_Depreciations.UnitNr) as NBV,
round(((Vehicle.value - (select SUM(
(CASE WHEN Vehicle_Depreciations.END_DATE >GETDATE()
THEN DATEDIFF(month,Vehicle_Depreciations.START_DATE,GETDATE())
ELSE DATEDIFF(month,Vehicle_Depreciations.START_DATE,Vehicle_Depreciations.END_DATE) END * Vehicle_Depreciations.Vehicle_Depreciation_Value))
FROM Vehicle_Depreciations where
Vehicle.unitnr = Vehicle_Depreciations.UnitNr))* 1.23),0) as NBVincVAT
from Vehicle
WHERE Vehicle.Type_Fleet <> 'FS' AND Vehicle.Status <> '4'
unitnr|platenr|groupid|brandId|modelId|category|kms|value|Type_Fleet|Date_Block|Dep|BlockedNBV|NBV|NBVincVAT|
30|08D25278|IDAR|CHEVROLET|EPICA| PAS| 84073|5536.77|OF| 2012-02-29| 1163.36|- 1641677.27| 4373.41| 5379|
294|11D19423|EDMN|FORD|FIESTA STYLE 1.25|PAS| 53922|9783.65|OF| NULL|2356.40|-1637430.39|7427.25|9136.0000|
July 26, 2012 at 10:59 am
It is pretty hard to help much without something to work with. We would need to see ddl, some sample data and desired output. Obfuscate your data to protect real data as appropriate. Take a look at the first link in my signature for best practices when posting a question.
I am sure we can help out.
Also fwiw your where clause makes this query non-SARGable. We can help with that too but need the details from you first.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 26, 2012 at 11:24 am
I'd have to have some sample data, and table structures to insert it into, to real nail this one down.
My first suspicion would be that the monthly depreciation has a different data definition than what you're looking for here. Like, if it's the value of the vehicle at the end of the month, instead of the number of dollars lost in value, then you'd end up with the wrong math here. But that's just a guess based on generic experience with unexpected math results in databases, since I don't know what the values in that column of your data look like.
If I were writing the query, I'd be inclined to get away from complex nested Case statements, and move towards using Cross Apply to do the math and the calculations of which date to use.
Something like:
CREATE TABLE #T
(StartDate DATE,
EndDate DATE);
INSERT INTO #T
(StartDate, EndDate)
VALUES ('2012-07-26', '2012-07-30'),-- Future end-date
('2012-07-10', '2012-07-20'),-- Past end-date
('2012-07-26', NULL);
-- No end-date
SELECT StartDate,
FirstEndDate.EndDate
FROM #T
CROSS APPLY (SELECT TOP (1)
CAST(EndDate AS DATE) AS EndDate
FROM (SELECT ISNULL(EndDate, GETDATE()) AS EndDate
UNION ALL
SELECT GETDATE()) AS EndDates
ORDER BY EndDate) AS FirstEndDate;
With that, the data and rules are in one place, generate a column you can error-check for business-validity, and you don't have to calculate it multiple times in multiple Case statements, you can just use the column over and over, just like any other column in a query.
The way the Cross Apply works is it first runs the inner query, which returns a set of 2 rows, one with the actual end date value, or GetDate() if that's null, the other with GetDate(), then it applies the outer query, which selects the top 1 of those two, ordered by earliest-to-latest. Handles all that logic in one go, and does so in a way that's re-usable.
Then, if you have the Deprectiation/Month in another table, you can Cross Apply to that table, multiply the datediff(month) value, and you should get what you need. Assuming the column is correctly defined for that math.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply