Top 10 Origin Destination Pair Based On Highest Load Grouped by Customer

Transact-SQL
1

For some reason this syntax is not working like I was expecting it to. I was trying to get the top 10 lanes (origin - destination pair) based on count(load_count) for each customer. Load status =3 means delivered and I was looking for only a certain timeframe.

select * from (
SELECT
CONCAT(L1.CITY, L1.STATE, L2.CITY, L2.STATE) as 'ODPair',
--L1.CITY as 'Origin City', L1.STATE as 'Origin State', L2.CITY as 'Destination City', L2.STATE as 'Destination State',
count(load_nbr) as Load_Count,
--[executed_mode], spot_flg,
row_number() over (partition by CONCAT(L1.CITY, L1.STATE, L2.CITY, L2.STATE) order by count(load_nbr)) as load_rank
FROM [Analytics].[TMSOP].[ENTSC_LOAD_TREND] E
INNER JOIN [Analytics].[TMSOP].[ENTSC_LOCATION_TREND] L1 ON L1.Location_key = E.origin_key
INNER JOIN [Analytics].[TMSOP].[ENTSC_LOCATION_TREND] L2 ON L2.Location_key = E.[destination_key]
INNER JOIN [Analytics].[TMSOP].[ENTSC_CARRIER] C ON C.carrier_nbr=E.carrier_nbr
INNER JOIN [Analytics].[TMSOP].[ENTSC_EQ_BRIDGE] B ON B.CustomerEquip = E.equipment AND B.CustomerMode=E.executed_mode
WHERE ([load_accept_dt] BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 00:00:00'
OR [load_accept_dt] BETWEEN '2022-01-01 00:00:00' AND '2022-01-31 00:00:00') and load_status='3'
group by CONCAT(L1.CITY, L1.STATE, L2.CITY, L2.STATE), spot_flg, executed_mode--, L1.CITY, L1.STATE, L2.CITY, L2.STATE
) load_ranks
where load_rank <=10

2

we don't have your data model, so no idea what you are looking for. As a wild guess, I would look at the row_number and maybe change the order by count(load_nbr) to include desc

3

That is not a safe thing to do. You could get false matches that way. Instead, you should just group by all the separate values: L1.CITY, L1.STATE, L2.CITY, L2.STATE

1 Like
4

my challenge was that to have city-state combination; otherwise i could have same city name in different states