Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Problem using max() function.

Author  Topic 

Generaltao
Starting Member

8 Posts

Posted - 2004-09-14 : 09:46:03
Okay, I ran into another snag here. This is still the same car leasing database. Now, my requirement is that I have to build a select statement that shows a list of vehicles with descriptions but also follow it with the largest maximum kilometers that was ever allowed in a lease terms for that vehicle.

Each Lease is linked to a table of set 'lease terms'. In this Terms table there is a field called TermMaxKM. Somehow I have to:

A)Link up vehicles table to the three description look-up tables to retrieve proper descriptions (Colour, Type, and Model tables)

B)Have Vehicles joined to their related leases (which they could potentially have many in their history.)

C) Join these Leases to the Terms table through the TermID field so that I can get the max KM for that term

D)Display only the HIGHEST VALUE of max KM from any lease that was issued for a vehicle.

My brain kind of hurts right now. I have the database diagram here, but my brain is having a hard time trying to figure out where to go from here. This is my groundwork, though it might be entirely off base:

quote:
SELECT Vehicles.VIN, Model_Desc = (Model.ModelDesc), Type_Desc = (Type.TypeDesc), Colour_Desc = (Colours.ColourDesc), Max_KM = (SELECT max(Terms.termmaxkm) FROM [Terms] WHERE Lease.VIN = Vehicles.VIN)
FROM Lease
INNER JOIN Terms
ON Lease.TermID = Terms.TermID
INNER JOIN Vehicles
ON Lease.VIN = Vehicles.VIN
INNER JOIN Model
on Vehicles.ModelID = Model.ModelID
INNER JOIN Type
ON Vehicles.TypeID = Type.TypeID
INNER JOIN Colours
ON Vehicles.ColourID = Colours.ColourID
WHERE Lease.TermID = Terms.TermID



This might be more of a logic problem than a technical one, but any help would be greatly appreciated.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-14 : 10:06:11
Keep A seperate from B,C, and D. A has no bearing on the logic of the SELECT statement. disregard for now, until you can get the math to work out right.

If the goal is to return 1 row per VIN, then you know that at some point you will need to group by VIN in a subquery, and for each VIN you need to return the MaxKM. Strip away everything else, and figure out how to return that result.

From what I can gather, I think it is this:


select
V.VIN, MAX(TermMaxKM) as MaxKM
from
Vehicles V
inner join
Lease L
on V.VIN = L.VIN
inner join
Terms T
on L.TermID = L.TermID


Test it, troubleshoot it, make sure it makes sense before moving on. remember, the goal for this 1 step is to return the MAX KM per VIN. when you 100% understand that code and get it to work, then you work on the next steps.

The next steps, then, are just to join the above SQL to your other tables and voila -- you have your final result:

(this is modified from the SQL you've posted) :


SELECT Vehicles.VIN, Model_Desc = (Model.ModelDesc),
Type_Desc = (Type.TypeDesc), Colour_Desc = (Colours.ColourDesc), B.MaxKM
FROM Lease
INNER JOIN Terms
ON Lease.TermID = Terms.TermID
INNER JOIN Vehicles
ON Lease.VIN = Vehicles.VIN

FROM Vehicles
INNER JOIN Model
on Vehicles.ModelID = Model.ModelID
INNER JOIN Type
ON Vehicles.TypeID = Type.TypeID
INNER JOIN Colours
ON Vehicles.ColourID = Colours.ColourID
INNER JOIN (the above SQL) B on Vehicles.VIN = B.VIN
WHERE Lease.TermID = Terms.TermID


WHen you build the above, add 1 table at a time, make sure it works, add the next. work on it step by step. too many people throw 15 tables together in a join all at once, and then when it doesn't work they randomly added GROUP BY's and WHERE clauses and all that w/o knowing what is going on. 1 step at a time, building from the inside out.

- Jeff
Go to Top of Page

Generaltao
Starting Member

8 Posts

Posted - 2004-09-14 : 10:09:57
Thank you very much! I'm trying to wrap my brain around the logic since database structure logic and database programming is all foreign to me. I feel like I'm being tossed from the nest without the power to fly yet!

I hope this works. Thanks for taking the time to answer my question
Go to Top of Page
   

- Advertisement -