Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

evaluating and combining values from two rows Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:35 AM
Points: 40, Visits: 154
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 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
For Sale 29 08D54321 OF 10607.12 2008-02-29 2012-02-29 2012-02-29 81.53 3913.44
For Sale 29 08D54321 OF 10607.12 2012-03-01 2012-08-30 2012-02-29 284.49 1422.45
For Sale 30 08D12346 OF 5536.77 2008-02-29 2012-02-29 2012-02-29 7.57 363.36
Post #1346656
Posted Friday, August 17, 2012 11:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 4,046, Visits: 9,204
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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1346690
Posted Friday, August 17, 2012 2:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:35 AM
Points: 40, Visits: 154
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.
Post #1346750
Posted Friday, August 17, 2012 3:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:12 PM
Points: 1,945, Visits: 3,180
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).


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1346769
Posted Friday, August 17, 2012 3:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 20,861, Visits: 32,891
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1346771
Posted Saturday, August 18, 2012 3:21 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 4,046, Visits: 9,204
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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1346885
Posted Monday, August 20, 2012 1:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:35 AM
Points: 40, Visits: 154
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.

Post #1347453
Posted Tuesday, August 21, 2012 12:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:36 PM
Points: 3,438, Visits: 5,393
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1347591
Posted Tuesday, August 21, 2012 7:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 4,046, Visits: 9,204
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
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





Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1347781
Posted Thursday, August 23, 2012 7:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:35 AM
Points: 40, Visits: 154
--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
Post #1349116
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse