sqlduplicatessql-server-2008-express

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.


Solution

  • 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'