15,747,637 members
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
Updated 6-Mar-23 5:59am

## Solution 1

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```