Help With Query Structure

P

P00HB33R

Senior Member
Joined
Jul 15, 2010
Messages
706
Reaction score
29
Location
Eloff
Hi All,

Im going to try to simplify this question as much as possible, but this one has me stumped.

I have 3 tables. A "Vehicles" Table, A "Vehicle Service Schedule" Table, and a "Loads Done By Vehicle" Table.

In the Loads Done Table, You select a vehicle, add the delivery address etc, and read in the start and end odometer reading.

The Service Schedule Table can have multiple entries per vehicle where you can specify the service type and amount of kilometers between each of the service types.

Now I need a query that pulls all vehicles that are due for the respective services specified in their schedule.

Firstly, I thought of running a query that groups the results by vehicle, then order the list so the latest record for each vehicle is first, then get that record for each vehicle. So now I have the latest vehicle load record, with their latest odometer reading. How am I going to query the records to get the ones that are due for a service. Bear in mind each vehicle can have multiple service types with varying kilometers between services.

Any help would be greatly appreciated. I am using Lightswitch, VB.NET with Visual Studio 2013.

Thanks in advance
 
You need to join on all tables, i assume all tables will hold a reference to the vehicles primary key? like VehicleSeqNo or VehicleReg etc.

then you need to filter results to return vehicles that meet their service criteria. so get the latest odometer reading and compare it to the service intervals, you can either have it match exactly or make it withing a certain range, i.e like 500 below or above etc.

this isn't great and i didn't even test it but something like this:

SELECT V.*
FROM Vehicles V, VechicleService VS, VehicleLoad VL
WHERE V.VehicleSeqNo = VS.VehicleSeqNo
AND V.VehicleSeqNo = VL.VehicleSeqNo
AND MAX(VL.KMEnd) % VS.KMInterval = 0

not even sure that will run correctly but you should get the idea.
 
Mike Smit said:
When trying to filter grouped results a partition by works well.
Click to expand...

I'm not sure how "partition by" would be suitable for this. Could you maybe provide an example? I'm genuinely curious as I don't use the "partition by" clause very often.

Personally, if I wanted to filter grouped criteria, I'd use the "having" clause.

OP, if you could give us your table scripts, we could probably write a reliable query for you. It would also make answering your question easier.
 
Messugga said:
I'm not sure how "partition by" would be suitable for this. Could you maybe provide an example? I'm genuinely curious as I don't use the "partition by" clause very often.

Personally, if I wanted to filter grouped criteria, I'd use the "having" clause.

OP, if you could give us your table scripts, we could probably write a reliable query for you. It would also make answering your question easier.
Click to expand...

Too many whiskies right now. Christmas has started :)
Will see what I can do tomorrow - mood permitting.
 
D3x! said:
You need to join on all tables, i assume all tables will hold a reference to the vehicles primary key? like VehicleSeqNo or VehicleReg etc.

then you need to filter results to return vehicles that meet their service criteria. so get the latest odometer reading and compare it to the service intervals, you can either have it match exactly or make it withing a certain range, i.e like 500 below or above etc.

this isn't great and i didn't even test it but something like this:

SELECT V.*
FROM Vehicles V, VechicleService VS, VehicleLoad VL
WHERE V.VehicleSeqNo = VS.VehicleSeqNo
AND V.VehicleSeqNo = VL.VehicleSeqNo
AND MAX(VL.KMEnd) % VS.KMInterval = 0

not even sure that will run correctly but you should get the idea.
Click to expand...

Hi Guys,

Thank you all for the information. Will try this suggestion a go after Christmas. Been giving me sleepless nights now.
 
Hi Guys,

Just to let you know, I got it working. Seems I was using the wrong approach. Did a little tweaking in the table layouts and got a perfectly working Query using 1 line of code.

You gotta love logic....
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X