August 17, 2012 at 10:32 am
I am running into a bit of trouble with the query below, would be great If you could offer some insight or advice on the best way forward.
Basically the two tables in use are Vehicle and Vehicle_Depreciations. There is one row per unitnr in the vehicle table, within the Vehicle_Depreciations table there is two rows per unitnr. These two rows have different monthly depreciation values based on different date periods. 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(vehicle.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.
Declare @date as datetime
set @date ='2012-07-31'
DECLARE @vehicle TABLE (unitnr int, value int, date_block varchar)
DECLARE @Vehicle_Depreciations TABLE (Start_Date datetime, End_Date datetime,Vehicle_Depreciation_Value int,unitnr int)
INSERT @Vehicle
SELECT 724, 9430,2012-07-23 UNION ALL
SELECT 725, 12430, null
INSERT @Vehicle_Depreciations
SELECT 2009-05-06, 2012-02-29 ,175,724 UNION ALL
SELECT 2012-03-01,2012-08-30 ,284, 724 UNION ALL
SELECT 2010-04-30, 2012-02-29,159,725 UNION ALL
SELECT 2012-03-01,2012-12-31,183,725
select 'For Sale' as Type, Vehicle.unitnr, Vehicle.platenr,Vehicle.Type_Fleet,
Vehicle.value,Vehicle_Depreciations.Start_Date,Vehicle_Depreciations.End_Date,Vehicle.Date_Block ,Vehicle_Depreciations.Vehicle_Depreciation_Value,
sum(CASE WHEN Vehicle_Depreciations.END_DATE >@DATE--checks if end_date is greater than the date passed
THEN DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,DATEADD(day,1,@DATE))--determines the months of depreciation from start_date till date passed
WHEN Vehicle_Depreciations.END_DATE > Vehicle.Date_Block-- checks if end_date is past Date_Blocked for sale
THEN DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,Vehicle.Date_Block) --if so, then determines the months of depreciation from start_Date till dat_blocked
ELSE DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,Vehicle_Depreciations.END_DATE+1) END * Vehicle_Depreciations.Vehicle_Depreciation_Value )AS depr --determines the months of depreciation between start_date and end_date
from Vehicle inner join Vehicle_Depreciations on Vehicle.unitnr = Vehicle_Depreciations.UnitNr
WHERE
Vehicle.Type_Fleet = 'of' --own fleet vehicles
AND Vehicle.Status <> '4' --excludes sold cars
and vehicle.Date_Block is not null
group by Vehicle.unitnr, Vehicle.platenr, Vehicle.groupid, Vehicle.brandId, Vehicle.modelId, Vehicle.category, Vehicle.kms, Vehicle.value, Vehicle.Type_Fleet,vehicle.Date_Block,Vehicle.purchase_date,Vehicle.Sales_Date,Vehicle.Invoice_Value,Vehicle.Invoice_Tax_Value,Vehicle.Extras_Value,Vehicle.Expenses,Vehicle.Another_Tax,Vehicle.Other_Extras_Value,Vehicle_Depreciations.End_Date,Vehicle_Depreciations.Start_Date,Vehicle_Depreciations.Vehicle_Depreciation_Value
TYPE UNITNR PLATENR TYPE_FLEET VALUE START-DATE END_DATE DATE_BLOCKED VEHICLE_DEPRECIATION_VALUE DEPR
For Sale2808D12345OF 10674.182008-02-29 2012-02-29 2012-07-24 112.475511.03
For Sale2808D12345OF 10674.182012-03-01 2012-08-30 2012-07-24 508.612543.05
For Sale2908D54321OF 10607.122008-02-29 2012-02-29 2012-02-29 81.533913.44
For Sale2908D54321OF 10607.122012-03-01 2012-08-30 2012-02-29 284.491422.45
For Sale3008D12346OF 5536.772008-02-29 2012-02-29 2012-02-29 7.57363.36
August 17, 2012 at 11:44 am
Is a good thing that you posted DDL, sample data and expected results.
But the problem is it's not matching with each other.
You might want to check your definition on date_block field from Vehicle table. Is a varchar with no length defined, that will truncate the values to a lendth of 1.
Another thing that had problems is that your query uses more fields than the vehicle table has (that might be a problem caused by simplifying the query to post it here).
The last thing, is that you have sample data for vehicles 724 and 725 and expected results for 28, 29 and 30.
You seem to be very close to your result, but I'm not sure on what is missing unless you give me the correct expected results.
August 17, 2012 at 2:19 pm
Hi Luiz, I just posted the ddl as a guide, the values may not be 100%, should of fine tuned them before posting,have no access at this exact time.Basically its the actual case statement im concerned with , I don't know if the actual logic within the sql is correct or if a case statement is even the right way to actually achieve a result, is there another way of doing it?
TYPE UNITNR PLATENR TYPE_FLEET VALUE START-DATE END_DATE DATE_BLOCKED VEHICLE_DEPRECIATION_VALUE DEPR
For Sale | 28 | 08D12345 | OF | 10674.18 | 2008-02-29 | 2012-02-29 | 2012-07-24 | 112.47 | 5511.03
For Sale | 28 | 08D12345 | OF | 10674.18 | 2012-03-01 | 2012-08-30 | 2012-07-24 | 508.61 | 2543.05
here is what i need
TYPE| UNITNR| PLATENR| TYPE_FLEET| VALUE| DATE_BLOCKED | DEPR
For Sale| 28| 08D12345 |OF| 10674.18| 2012-07-24 112.47| 7938
The key figure here is the result DEPR 7938 .This number comes from the first period of depreciation 2008-02-29 to 2012-02-29 is 48 months which is then * 112.47 this equals 5398. This figure is added to the second period of depreciation 2012-03-01 to (2012-07-24(date vehicle blocked for sale) which is 2540. This adds up to 7938
This is where I need to get to, so anything that can keep me moving is appreciated.
August 17, 2012 at 3:34 pm
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).
Mr. Celko, either your spell check isn't working or your eyes are still suffering from the surgery. You may want to take a break. Have a happy Friday and a good weekend.
August 18, 2012 at 3:21 pm
Mr. Celko,
The main problem in here wouldn't be the DDL as we cannot see the original table just a simplified version of it. I'm pretty sure the OP shouldn't be changing the database structure one table at a time, that's just pointless and would imply lots of work on SQL Server and on the front ends. If you can't give a solution (or read the problem), please avoid posting anything. I'm sure that when someone wants to design a database with little knowledge on database design, they can go to the Stairway you wrote.
To Thomas,
I'm having some trouble with your posts, as there are three main problems I found:
1. Your calculations are wrong or have some logic you didn't post (48 times 112.47 equals 5398.56 not 5398 and 508.61 times 5 equals 2543.05 not 2540) it might seem very few difference, but it's still a difference.
2. Your values are declared int but you might want to use a numeric/decimal data type to avoid truncation.
3. Your dates can have some trouble since DATEDIFF( mm, ' 20080229', '20120229') will give 49 and not 48. (I'm sure this was happening on a 2005 version, but I'm trying to reproduce this problem and won't happen in 2012 but I don't have a 2008 here).
Be sure to test each part of your code before using it. And take care of your math.;-)
August 20, 2012 at 1:47 pm
Mr celko, "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. "
This is my problem, if all data was on the same row my job would be easier. I cannot change the underlying table structure, so I have to make the best of it. I understand what you are saying about "vins" etc, but I didn't design the tables so I have to suffer it.
Luiz I really need to get this query to work, so if anybody can tell me what they exactly need me to post. I understand the math was not 100% I just put it together in haste as a guide while away with my family the weekend. Will I post exact ddl and some obfuscated results and then The exact results I require,would this help?Thanks again everybody.
August 21, 2012 at 12:55 am
Not sure but you may be able to do something like this assuming there is no overlap in dates in your @Vehicle_Depreciations table:
;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
If you need total "to date" depreciation for a vehicle, then comment out the SELECT, uncomment the SELECT below it, uncomment the GROUP BY and remove m from the ORDER BY list.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 21, 2012 at 7:26 am
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
August 23, 2012 at 7:53 am
--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
August 23, 2012 at 8:50 am
The problem was on your conditions to choose the valid end_date.
As it would become more and more complicated, I changed the way it was made. Try to understand what it's doing and explain it here before you use it.
Declare @date as datetime
set @date ='20120731'
DECLARE @vehicle TABLE (
unitnr int,
value decimal( 18, 8),
date_block datetime,
type_fleet char(2),
platenr char(9))
DECLARE @Vehicle_Depreciations TABLE (
Start_Date datetime,
End_Date datetime,
Vehicle_Depreciation_Value decimal( 18, 8),
unitnr int)
INSERT @Vehicle
SELECT 29, 8057.85, '20120406', 'of', '08D12345'
INSERT @Vehicle_Depreciations
SELECT '20100301', '20120229' ,151.45,29 UNION ALL
SELECT '20120301', '20120801' ,145.39, 29 UNION ALL
SELECT '20120801', '20140801' ,1450.39, 29 ; --I changed the periods for testing
WITH Depreciations AS(
SELECT 'For Sale' as Type,
v.unitnr,
v.value,
v.platenr,
v.Type_Fleet,
vd.Start_Date,
CASE WHEN vd.END_DATE < @DATE AND vd.END_DATE < v.date_block
THEN vd.END_DATE
WHEN v.Date_Block < vd.END_DATE AND v.Date_Block < @date
THEN v.Date_Block
ELSE @date END end_date,
vd.Vehicle_Depreciation_Value
FROM @vehicle v
JOIN @Vehicle_Depreciations vd on v.unitnr = vd.UnitNr
WHERE v.Date_Block is not null
AND vd.Start_Date < @DATE --two new conditions to avoid undesired results
AND vd.Start_Date < v.date_block)
SELECT Type,
unitnr,
value,
platenr,
type_fleet,
SUM( (DATEDIFF( MM, Start_Date, end_date)
+ CASE WHEN end_date > DATEADD( MM, DATEDIFF( MM, Start_Date, end_date), Start_Date)
THEN 1 ELSE 0 END)
* Vehicle_Depreciation_Value)
FROM Depreciations
GROUP BY Type,
unitnr,
value,
platenr,
type_fleet
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply