join of two queries missing values

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

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.