August 8, 2012 at 5:17 am
This statement calculates and returns the depreciated valus for a list of vehicles, The part i am now stuck on is highlighted in bold.
Basically i Need this to exclude all vehicles that were sold in the month previous to the @DATE parameter specified.I've been playing around with it for a while and can't get it going, any help is appreciated. Ideally I want to do the ecluding in the where statement. If anymore info is required just let me know. I included one row from my result set with the column i need excluded in bold.
DECLARE @DATE VARCHAR(25)
SET @DATE = '2012-08-01 23:59:59'
select 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) as SalesPrice,
Vehicle.value as Value,
Vehicle.Extras_Value as Addon,
Vehicle.Expenses as VRT1,
Vehicle.Another_Tax as Our_VRT,
Vehicle.Other_Extras_Value as 'Rebate/Excess',
(Vehicle.value +Vehicle.Extras_Value + Vehicle.Expenses + Vehicle.Another_Tax + Vehicle.Other_Extras_Value) AS NetCost,
@DATE AS Date,
(select SUM(
(CASE WHEN Vehicle_Depreciations.END_DATE >@DATE
THEN DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,DATEADD(day,1,@DATE))
ELSE DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,Vehicle_Depreciations.END_DATE+1) END * Vehicle_Depreciations.Vehicle_Depreciation_Value))
FROM Vehicle_Depreciations where
Vehicle.unitnr = Vehicle_Depreciations.UnitNr) as Depr,
(select top 1 min
(CASE WHEN Vehicle_Depreciations.END_DATE >@DATE
THEN Vehicle_Depreciations.Vehicle_Depreciation_Value
ELSE Vehicle_Depreciations.Vehicle_Depreciation_Value END )
FROM Vehicle_Depreciations where
Vehicle.unitnr = Vehicle_Depreciations.UnitNr) as MonthlyDep,
Vehicle.value - (select SUM(
(CASE WHEN Vehicle_Depreciations.END_DATE >@DATE
THEN DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,DATEADD(day,1,@DATE))
ELSE DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,Vehicle_Depreciations.END_DATE+1) 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 >@DATE
THEN DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,DATEADD(day,1,@DATE))
ELSE DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,Vehicle_Depreciations.END_DATE+1) 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 NOT EXISTS (select * from vehicle where Sales_Date >DATEADD(MONTH,-1,@DATE))
14506D0000 CWMNCHEVROLETLACETTI 1.6PAS 934677336.34OF 2012-05-10 00:00:00.000
2009-04-30 00:00:00.0002012-07-09 00:00:00.0003600.007336.34NULL0.000.00NULLNULL2012-
08-01 23:59:595524.95127.571811.392228.0000
August 8, 2012 at 6:59 am
Im sure im missing something but why not just add something like
and Vehicle.Sales_Date > DATEADD(MONTH, -1, @DATE)
instead of the
AND NOT EXISTS (select * from vehicle where Sales_Date >DATEADD(MONTH,-1,@DATE))
/T
August 8, 2012 at 7:07 am
How about this?
WHERE V.Type_Fleet <> 'FS'
AND Sales_Date > @DATE
AND Sales_Date <= DATEADD(MONTH, - 1, @DATE)
I'm not sure, but I believe this code will perform poorly with all those subqueries in it.
I'm sure there's a way to improve it, let me work on it for a while.
August 8, 2012 at 7:22 am
I'm not sure if this would work correctly, because I have no DDL or sample data, but if it does, it should improve the performance and would make your query easier to read.
Try it, undestand it and test it before you use it.
;WITH Depreciations AS (
SELECT UnitNr,
SUM(CASE
WHEN vd.END_DATE > @DATE
THEN DATEDIFF(MONTH, vd.START_DATE, DATEADD(day, 1, @DATE))
ELSE DATEDIFF(MONTH, vd.START_DATE, vd.END_DATE + 1)
END * vd.Vehicle_Depreciation_Value )AS Depr,
MIN(CASE --Why are you using this case? drop it if it's not needed.
WHEN vd.END_DATE > @DATE
THEN vd.Vehicle_Depreciation_Value
ELSE vd.Vehicle_Depreciation_Value
END) AS MonthlyDep
FROM Vehicle_Depreciations
GROUP BY UnitNr)
SELECT V.unitnr,
V.platenr,
V.groupid,
V.brandId,
V.modelId,
V.category,
V.kms,
V.value,
V.Type_Fleet,
V.Date_Block,
V.purchase_date,
V.Sales_Date,
(V.Invoice_Value + V.Invoice_Tax_Value) AS SalesPrice,
V.value AS Value,
V.Extras_Value AS Addon,
V.Expenses AS VRT1,
V.Another_Tax AS Our_VRT,
V.Other_Extras_Value AS 'Rebate/Excess',
(V.value + V.Extras_Value + V.Expenses + V.Another_Tax + V.Other_Extras_Value) AS NetCost,
@DATE AS DATE,
d.Depr,
d.MonthlyDep,
V.value - d.Depr AS NBV,
round((V.value - d.Depr) * 1.23, 0) AS NBVincVAT
FROM Vehicle v
JOIN Depreciations d ON V.unitnr = d.UnitNr
WHERE V.Type_Fleet <> 'FS'
AND Sales_Date <= DATEADD(MONTH, - 1, @DATE)
August 8, 2012 at 9:14 am
A couple other suggestions. You should not declare @Date as varchar and turn around and force implicit conversions to datetime.
DECLARE @DATE VARCHAR(25)
SET @DATE = '2012-08-01 23:59:59'
Instead declare it as the proper datatype.
DECLARE @DATE datetime
SET @DATE = '2012-08-01 23:59:59'
Also your where clause is not sargable.
WHERE V.Type_Fleet <> 'FS'
The <> will force an index scan even if you have index of Type_Fleet. Unless the possible values are a lot you would have better performance by making your check a positive check instead.
Something like
where V.Type_Fleet in ('AS', 'FG', 'Whatever')
There appears a lot of subqueries that could use some performance boosting efforts as well.
_______________________________________________________________
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/
August 8, 2012 at 9:35 am
Sean Lange (8/8/2012)
A couple other suggestions. You should not declare @Date as varchar and turn around and force implicit conversions to datetime.
I couldn't believe that @Date was declared as a varchar, I had to return to the OP to prove it.
I'm afraid the world might be ending. :laugh:
August 8, 2012 at 9:43 am
Luis Cazares (8/8/2012)
Sean Lange (8/8/2012)
A couple other suggestions. You should not declare @Date as varchar and turn around and force implicit conversions to datetime.I couldn't believe that @Date was declared as a varchar, I had to return to the OP to prove it.
I'm afraid the world might be ending. :laugh:
LOL.
I sort of glossed over all the other issues in this thing.
For example:
select top 1 min
(CASE WHEN Vehicle_Depreciations.END_DATE >@DATE
THEN Vehicle_Depreciations.Vehicle_Depreciation_Value
ELSE Vehicle_Depreciations.Vehicle_Depreciation_Value END )
FROM Vehicle_Depreciations where
Vehicle.unitnr = Vehicle_Depreciations.UnitNr
That is a subquery. Really top 1 min()??? Just in case there is more than 1 minimum value? Sort of looks like maybe the top 1 and MIN were added as reaction to the "subquery returned more than 1 row" issue instead of what is actually required. Just in case you missed the details, look closely at the criteria of the case statement. The two conditions are the same. If the EndDate > @Date get the same value as when it is less.
_______________________________________________________________
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/
August 8, 2012 at 10:46 am
my bad, just seen that there and changed immediatly. followed the suggestions to no avail, ive tried
-AND NOT EXISTS (select * from vehicle where Sales_Date >DATEADD(MONTH,-1,@DATE))
--AND Sales_Date > @DATE
--AND Sales_Date <= DATEADD(MONTH, - 1, @DATE)
-- and Sales_Date < DATEADD(MONTH, -1, @DATE)
Just started using SQL so I don't mean to offend the fanatics.
August 8, 2012 at 11:26 am
There's no offense, I hope you didn't take the comments on a bad way.
It's clear that you don't have much experience on SQL Server, but I hope you could learn something from this. There where several things that you could improve and hopefully you noticed them.
Try to review your code after you made it so you can find the mistakes you've made (probably when you're trying to change or correct it). And never use code you don't fully understand.;-)
August 8, 2012 at 12:12 pm
Nope no offense taken. It seems that you still have some issues getting the information you want out of your query?
_______________________________________________________________
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/
August 8, 2012 at 9:59 pm
Here's an article that shows how you can calculate a depreciation schedule for an asset using an rCTE: http://www.sqlservercentral.com/articles/T-SQL/90955/.
That example (among many others) is about mid-way through.
It can easily be modified to calculate accumulated depreciation at any point in time.
If this is of interest and usable to you, I can show you how.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply