How to Merge & Aggregate Inbound/Outbound Data with Unique Dates in SQL?

Merge Tables and Unique Date Values in SQL

The challenge involves merging two tables, Inbound and Outbound, while ensuring that the resulting table displays unique dates.

The provided VBA code successfully merges the tables using UNION ALL. However, to make the dates unique, a modification is needed.

The revised query employs both UNION ALL and GROUP BY. Specifically, it combines data from both tables into a single dataset using UNION ALL, then groups the results by Date and Product. This ensures that each combination of Date and Product appears only once in the merged table.

The final query is as follows:

SELECT Date, Product, SUM(Inbound) AS Inbound, SUM(Outbound) AS Outbound
FROM ((SELECT Inbound_Date AS Date, Product, SUM(Quantity) AS Inbound, 0 AS Outbound
      FROM Inbound
      GROUP BY 1, 2
     ) UNION ALL
     (SELECT Outbound_Date, Product, 0 AS Inbound, COUNT(*) AS Outbound
      FROM Outbound
      GROUP BY 1, 2
     )
    ) AS io
GROUP BY Date, Product;

With this modification, the merged table will display unique dates while maintaining the desired data aggregation.

The above is the detailed content of How to Merge & Aggregate Inbound/Outbound Data with Unique Dates in SQL?. For more information, please follow other related articles on the PHP Chinese website!