Calculate diff time for combinations of two columns

Question

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.

Posted 5-Mar-23 10:54am

Member 15942526

Updated 6-Mar-23 5:59am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2023-03-06T05:59:00

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

SQL

select 
		 a1.Origin
		,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
...etc