bham3dman bham3dman -4 years ago 41
SQL Question

How can I select rows and group duplicate results from 2 tables based on data in a third table?

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.

Answer Source
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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download