How do I combine multiple tables? (First has data from this month, second has all other previous data)

I am looking to create a query that shows shipping number, the container ID, the tracking number, the location it was last moved to, what time it was moved, and who moved it.

Here’s the issue. We recently backed up or transaction history onto another table for anything that’s over 30 days old.

So I have the table transaction_history which gives me everything from today to 30 days ago, and I have the table AR_transaction_history, which gives me everything else (starting from 31 days ago.)

I need to be able to create prompts for the user to input either the container ID, tracking number, or shipping ID.

I need help joining the two tables to create 1 table with all the records. I tried union all and it does not work with my prompts. I tried an isnull statement and that didn’t work either. Here is the code.

select 
  th.reference_id,
  th.container_id 'Container ID', 
  sc.tracking_number 'Tracking Number',
  max(th.DATE_TIME_STAMP) 'Time of Last Touch', 
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.user_name
  END AS 'User Name',
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.location
  END AS 'Location'
from TRANSACTION_HISTORY th
inner join TRANSACTION_HISTORY th1 on th1.CONTAINER_ID = th.CONTAINER_ID
inner join SHIPPING_CONTAINER sc on sc.CONTAINER_ID = th.CONTAINER_ID
group by th.container_id, sc.tracking_number, th1.DATE_TIME_STAMP, th1.USER_NAME, th1.LOCATION, th.REFERENCE_ID
Having
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.user_name
  END is not null

UNION ALL

select 
  th.reference_id,
  th.container_id 'Container ID',
  sc.tracking_number 'Tracking Number',
  max(th.DATE_TIME_STAMP) 'Time of Last Touch', 
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.user_name
  END AS 'User Name',
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.location
  END AS 'Location'
from AR_TRANSACTION_HISTORY th
inner join AR_TRANSACTION_HISTORY th1 on th1.CONTAINER_ID = th.CONTAINER_ID
inner join AR_SHIPPING_CONTAINER sc on sc.CONTAINER_ID = th.CONTAINER_ID
group by th.container_id, sc.tracking_number, th1.DATE_TIME_STAMP, th1.USER_NAME, th1.LOCATION, th.REFERENCE_ID
Having
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.user_name
  END is not null

Answer

Do UNION ALL in a subquery, and leave the rest of your original query untouched. This is the simplest way to proceed, without reviewing the whole logic of your (aggregated) query.

SELECT
....
FROM
    (
        SELECT * FROM TRANSACTION_HISTORY
        UNION ALL SELECT * FROM AR_TRANSACTION_HISTORY
    ) as th
    INNER JOIN SHIPPING_CONTAINER sc on sc.CONTAINER_ID = th.CONTAINER_ID
GROUP BY ...

Note: in general, SELECT * and UNION ALL do not get along well. This answer assumes that tables TRANSACTION_HISTORY and AR_TRANSACTION_HISTORY have exactly the same structure (columns and data types).

Advertisement

Answer