I need help joining these two queries. Here’s the first dataset with its query:
Terminal Inbound Shipments Inbound Revenue Albany 21 2785.51 Austin 7 1115.23
:
SELECT t.[Origin Terminal Name] as 'Terminal', COUNT(t.[Pro Number]) as 'Inbound Shipments', SUM(t.[Total Revenue]) as 'Inbound Revenue' FROM [AX2cTest].[dbo].[AAATRANSPORTTABLE_V] t where t.[Initial Rated Date/Time] >= '2020-03-19 02:00:00.000' and t.[Initial Rated Date/Time] <= '2020-03-20 01:59:00.000' GROUP BY t.[Origin Terminal Name] ORDER BY t.[Origin Terminal Name] ASC
and second:
Terminal Outbound Shipments Outbound Revenue Abilene 4 1609.53 Albany 12 4215.04 Asheville 15 2258.05 Augusta 10 1592.53 Austin 4 1198.11
:
SELECT
t.[Destination Terminal Name] as 'Terminal',
COUNT(t.[Pro Number]) as 'Outbound Shipments',
SUM(t.[Total Revenue]) as 'Outbound Revenue'
FROM [AX2cTest].[dbo].[AAATRANSPORTTABLE_V] t
where t.[Initial Rated Date/Time] >= '2020-03-19 02:00:00.000' and t.[Initial Rated Date/Time] <= '2020-03-20 01:59:00.000'
GROUP BY t.[Destination Terminal Name]
Order by t.[Destination Terminal Name]
My query produces this result:
Terminal Inbound Shipments Inbound Revenue Outbound Shipments Outbound Revenue Albany 21 2785.51 12 4215.04 Austin 7 1115.23 4 1198.11
I need to return as
Terminal Inbound Shipments Inbound Revenue Outbound Shipments Outbound Revenue Abilene 0 0 4 1609.53 Albany 21 2785.51 12 4215.04 Asheville 0 0 15 2258.05 Augusta 0 0 10 1592.53 Austin 7 1115.23 4 1198.11
I can’t figure out why my query is leaving out the records with no Inbound data. I believe a right join is correct. It seems like an issue with using the correct Terminal field. I think a second pair of eyes would help.
SELECT
t2.Terminal as 'Terminal',
COUNT(t.[Pro Number]) as 'Inbound Shipments',
SUM(t.[Total Revenue]) as 'Inbound Revenue',
max(t2.[Outbound Shipments]) as 'Outbound Shipments',
max(t2.[Outbound Revenue]) as 'Outbound Revenue'
FROM [AX2cTest].[dbo].[AAATRANSPORTTABLE_V] t
RIGHT JOIN (SELECT
t2.[Destination Terminal Name] as 'Terminal',
COUNT(t2.[Pro Number]) as 'Outbound Shipments',
SUM(t2.[Total Revenue]) as 'Outbound Revenue'
FROM [AX2cTest].[dbo].[AAATRANSPORTTABLE_V] t2
where t2.[Initial Rated Date/Time] >= '2020-03-19 02:00:00.000' and t2.[Initial Rated Date/Time] <= '2020-03-20 01:59:00.000'
GROUP BY t2.[Destination Terminal Name]) t2 on t.[Origin Terminal Name] = t2.Terminal
where t.[Initial Rated Date/Time] >= '2020-03-19 02:00:00.000' and t.[Initial Rated Date/Time] <= '2020-03-20 01:59:00.000'
GROUP BY t2.Terminal
ORDER BY t2.Terminal ASC
Advertisement
Answer
You seem to want:
select d2.terminal,
coalesce(d1.inbound_shipments, 0), coalesce(d1.inbound_revenue, 0) as outbound_revenue,
coalesce(d2.outbound_shipments, 0), coalesce(d2.outbound_revenue, 0) as outbound_revenue
from dataset2 d2 left join
dataset1 d1
on d2.terminal = d1.terminal ;
Your problem may be incorporating where conditions into the outer joins. I strongly recommend using LEFT JOIN. Conditions on the first table go in the WHERE clause. Conditions on the second (and subsequent tables) go in the ON clause.
I’m not sure what your code has to do with the problem you stated.