How to identify lanes with same starts and ends but with different stops?

[RoadyMayhem]

Hi all,

I been puzzling for quite some time now on the following problem, but I
can't find a correct solution. I hope anyone in this group can help.

my problem:

1: A truck collects goods from a plant (A) and delivers the goods at a
destination (Z). The route the truck takes is called a 'lane'.
A lane has a cost.
So, in a table I would have a record for LaneID, Start, End, Cost

2: However, it is also possible that a truck goes from A to (second
plant) B, before it delivers the goods at destination Z.
The lane is now A --> B --> Z.
The cost for this lane is of course less than the sum of lanes A--> B +
B --> Z.


3: To make is even more complicated, it is also possible that a truck
collects the goods at A, collects more goods at B, delivers a group of
goods at Y and delevers the rest at Z.
This lane would consist of A --> B, B --> Y, Y --> Z.

4: Three destinations is also possible in different ways:
A --> X, X --> Y, Y --> Z.
A --> B, B --> X, X --> Y, Y --> Z.

All described lanes have different prices, other than the sum of the
'sublanes'. In my table (and probable related table) each lane should
have a unique identifier and a price, to make it possible to refer to a
lane when a truck takes the same route.

In the reporting I want to be able to calculate the cost for each
ordered and delivered good as a percentage of the lane cost.

I hope you understand my problem and am open to any suggestions or help.

Thanks in advance,

Sybolt

[AaronKempf.com | CareerDBA.com | FullStackWP.com]

this problem is discussed in nearly every computer science course known to man.

the right answer is to choose a database that supports the Georgraphy Datatype.

otherwise, you're SOL

I vote for SQL Server Express, after 2008 with the introduction of R2 that feature allowed Bing Maps to be built quite easily.

I don't know if it's STILL supported. but I've used that same product with google maps quite a bit.