Help With Query Structure

P00HB33R

Senior Member
Joined
Jul 15, 2010
Messages
680
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
 

D3x!

Banned
Joined
Jul 18, 2010
Messages
18,617
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.
 

Messugga

Honorary Master
Joined
Sep 4, 2007
Messages
12,033
When trying to filter grouped results a partition by works well.

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.
 

DominionZA

Executive Member
Joined
May 5, 2005
Messages
8,308
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.

Too many whiskies right now. Christmas has started :)
Will see what I can do tomorrow - mood permitting.
 

P00HB33R

Senior Member
Joined
Jul 15, 2010
Messages
680
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.

Hi Guys,

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

P00HB33R

Senior Member
Joined
Jul 15, 2010
Messages
680
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....
 
Top