I have 3 tables (SQL Server 2008 Express): CustomerActivity, EmployeeActivity, & ShipperActivity
CustomerActivity has data similar to this:
CustomerName OrderNumber ActivityDate ------------ ----------- ----------------------- ABC Company 00001 2012-02-15 11:02:15.000 ABC Company 00005 2012-02-15 12:15:01.000 XYZ Company 00008 2012-02-15 14:02:03.000 XYZ Company 00008 2012-02-15 14:08:24.000
EmployeeActivity has data similar to this:
EmployeeName OrderNumber ActivityDate ------------ ------------ ----------------------- John Smith 00001 2012-02-15 11:00:39.000 Jane Doe 00008 2012-02-15 11:02:15.000 John Smith 00008 2012-02-15 13:25:01.000 Jane Doe 00005 2012-02-15 14:12:13.000 Jane Doe 00008 2012-02-15 14:28:34.000
ShipperActivity has data similar to this:
ShipperName OrderNumber ActivityDate ------------ ----------- ----------------------- BigShipper 00008 2012-02-15 10:03:44.000 LittleShipper 00005 2012-02-15 12:05:22.000 BigShipper 00008 2012-02-15 13:45:34.000 USShipper 00001 2012-02-15 14:11:23.000
Considering a situation in which I want to retrieve a list of companies and employees affected by shipping activity on 2012-02-15, how should I craft the SQL statement?
Desired Output:
Affected Users -------------- ABC Company Jane Doe John Smith XYZ Company
I have tried so many SQL statements but keep falling short.
SELECT ca.CustomerName AS [Affected Users]
FROM CustomerActivity ca
INNER JOIN ShipperActivity sa
ON ca.OrderNumber = sa.OrderNumber
WHERE sa.ActivityDate >= '2012-02-15 00:00:00'
AND sa.ActivityDate < '2012-02-16 00:00:00'
UNION
SELECT ea.EmployeeName AS [Affected Users]
FROM EmployeeActivity ea
INNER JOIN ShipperActivity sa
ON ea.OrderNumber = sa.OrderNumber
WHERE sa.ActivityDate >= '2012-02-15 00:00:00'
AND sa.ActivityDate < '2012-02-16 00:00:00'