Click here to Skip to main content
15,747,637 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need to calculate the time it takes for trucks to go from one station to another. I want to select any origin and destination in my dataset and calculate the average time it took for all trucks.

This is how the data could look like this:

Truck Number	Origin	Destination	Departure	Arrival
1	A	B	01-01-2022	05-01-2022
1	B	C	10-01-2022	15-01-2022
1	C	D	16-01-2022	20-01-2022
1	D	E	20-01-2022	22-01-2022
2	A	B	15-01-2022	25-01-2022

What I have tried:

We would need to get all the combinations from origins to destinations for the next X destination for a particular origin. It would be great if it could have a variable so we can set that we want the following X destinations. In the example it would be x=3 since we calculate the different between A and B C D but not AE:

Truck Number	Origin	Destination	Transit Time
1	A	B	5 days
1	A	C	15 days
1	A	D	20 days
1	B	C	5 days
1	B	D	10 days
1	B	E	12 days
1	C	D	4 days
1	C	E	6 days
2	A	B	10 days
Main question would be how to calculate all combinations for x stops in the future.
Updated 6-Mar-23 5:59am

1 solution

To get all of the combinations from origins to destinations first get a list of all possible origins and destinations by doing a UNION[^] selecting first the Origin from the table and then the Destination. You might (should) already have a table of the location points so you could use that.

Store that data in a temporary table, a table variable or use a CTE[^]

You can then use a CROSS JOIN[^] on that data to get all combinations. In this instance don't forget to filter out WHERE t1.Location<> t2.Location

You can give each destination for each origin a number using ROW_NUMBER[^] which you could then use to filter as you describe.

Example - If I have something that contains all locations I could do something like this
		,a2.Origin as Destination
		,ROW_NUMBER() OVER (partition by a1.Origin order by a1.Origin,a2.Origin) as rn
	from allLocations a1
	cross join allLocations a2
	where a1.Origin <> a2.Origin
which would give me a result set similar to
Origin	Destination	rn
A		B			1
A		C			2
A		D			3
A		E			4
B		A			1
B		C			2
B		D			3
B		E			4
C		A			1
Share this answer
Maciej Los 7-Mar-23 15:53pm    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900