Combining two rows to get one result

Question

Post reply

Combining two rows to get one result

thomasrichardson2000

SSC Eights!

Points: 808
More actions
July 26, 2012 at 3:13 am

#259380

Hi, I'm really stuck on this and could do with some help. Here is my Code.
SELECT Vehicle.unitnr, Vehicle.platenr, Vehicle.groupid, Vehicle.brandId, Vehicle.modelId, Vehicle.category, Vehicle.kms, Vehicle.value, Vehicle.Type_Fleet,
Vehicle.Date_Block, Vehicle_Depreciations.Start_Date, Vehicle_Depreciations.End_Date,GETDATE()AS Todays,
Vehicle_Depreciations.Vehicle_Depreciation_Percentage, Vehicle_Depreciations.Vehicle_Depreciation_Value, Vehicle_Depreciations.Extras_Depreciation_Percentage,
Vehicle_Depreciations.Extras_Depreciation_Value,
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 AS diff
FROM Vehicle INNER JOIN
Vehicle_Depreciations ON Vehicle.unitnr = Vehicle_Depreciations.UnitNr
WHERE Vehicle.Type_Fleet <> 'FS' AND Vehicle.Status <> '4'
unitnr platenr groupid brandId value Start_Date End_Date Vehicle_Depreciation_Value diff
1 11D18972 FDMD AUDI 25304.13 2011-04-30 2012-02-29 422.73 10
1 11D18972 FDMD AUDI 25304.13 2012-03-01 2013-12-31 465.00 4
Basically what I am trying to do is get the total depreciation for a vehicle, the only problem is that there is two rows(same unitnr) for each vehicle that calculate depreciation based on different values and time periods, how do I combine these two rows so I can calculate the first period of depreciation and then calculate the second period of depreciation and then add the two together to get my total, do I need to do this in report builder or is there a way to strip it down in SSMS. Any help is welcome, being thrown in at the deep-end here! I have put in an example of a vehicle in the result set(less a few columns)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

thomasrichardson2000 SSC Eights! Points: 808 More actions · Answer 1

thomasrichardson2000

SSC Eights!

Points: 808

July 26, 2012 at 7:07 am

#1517907

got it sorted.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

thomasrichardson2000 (7/26/2012)
got it sorted.

Sometimes all it takes is the extra thought required to write the problem down. Would you like to share your solution with us Thomas? I'm sure the folks here will give you some pointers for improving your code.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dwain Camps SSC Guru Points: 86978 More actions · Answer 3

thomasrichardson2000 (7/26/2012)
got it sorted.

Glad you got it sorted. If you're interested in a way to generate a depreciation schedule (including accumulated depreciation, which was the value you were seeking), take a look at this article:

http://www.sqlservercentral.com/articles/T-SQL/90955/

The rCTE presented could be cut off at any period in the schedule and you could select out only that record containing the value of the accumulated depreciation.

Of course, different scenarios require the use of different depreciation methods. Check with your accounting department to be sure.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

Few days back, I posted how to combine two consecutive rows and find result. Pls have a look, might be useful:

http://sqlpathy.wordpress.com/2012/07/22/operating-on-consecutive-rowscolumns/

I am sure there are many ways of doing a single task, this is just one of them 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter