An interesting and complex issue with SQL Server 2005 self-join

Question

I have a table called OrdersToCall

Data Types: all bigints except date, which is a date-time

| -Order Num- | ---- Date --- | - Primary Ph - | Secondary Ph | Alternate Ph
| ---- 101 ---- | 07/02/2010 | 925-515-1234 | 916-515-1234 | 707-568-5778  
| ---- 102 ---- | 07/02/2010 | 925-888-4141 | 925-888-4141 | 000-000-0000
| ---- 103 ---- | 07/02/2010 | 000-000-0000 | 000-000-0000 | 510-555-4575  
| ---- 104 ---- | 07/02/2010 | 415-789-5454 | 415-707-5588 | 735-874-9566
| ---- 105 ---- | 07/02/2010 | 925-887-7979 | 925-887-7979 | 925-887-7979

and I have another table called PhoneNumCalled

| -AgentID- | ---- Date ---- | -Dialed Number |
| -145564-- | 07/02/2010 | 925-515-1234 |
| -145564-- | 07/02/2010 | 707-568-5778 |
| -145566-- | 07/02/2010 | 925-888-4141 |
| -145567-- | 07/02/2010 | 510-555-4575 |
| -145568-- | 07/02/2010 | 415-789-5454 |
| -145568-- | 07/02/2010 | 415-707-5588 |
| -145568-- | 07/02/2010 | 735-874-9566 |
| -145570-- | 07/02/2010 | 925-887-7979 |
| -145570-- | 07/02/2010 | 925-887-7979 |

Now my task: I want to calculate how many Order Numwere called and create a table based on the results.

So, for example, if agent 1234 named all 3 numbers in 1 order, which would still count only 1 order for this agent. The ratio is 1: 1. As soon as the phone number is called, it is considered 1 order. Regardless of whether all 3 were called, the agent only needs to call 1 phone number to receive credit for the order.

3 1/2 , .

( ):
, :

--Delete and recreate the CombinedData table created yesterday
Insert into the CombinedData table
Select Order Num, Date, Primary Ph as Phone
 from OrdersToCall
Union
Select Order Num, Date, Secondary Ph as Phone
 from OrdersToCall
Union
Select Order Num, Date, Alternate Ph as Phone
 from OrdersToCall
Delete from the CombinedData table
 where phone in ('000-000-0000', '999-999-9999')

, , 2 .

.

Answer 1

, , :

SELECT c.AgentId, COUNT(DISTINCT o.[Order Num]) AS [Orders per Agent]
FROM OrdersToCall o
JOIN PhoneNumCalled c ON c.[Dialed Number] = o.[Primary Ph]
                      OR c.[Dialed Number] = o.[Secondary Ph]
                      OR c.[Dialed Number] = o.[Alternate Ph]
GROUP BY c.AgentId

, , :

SELECT c.AgentId, c.Date, COUNT(DISTINCT o.[Order Num]) AS [Orders per Agent]
FROM OrdersToCall o
JOIN PhoneNumCalled c ON (c.[Dialed Number] = o.[Primary Ph]
                      OR c.[Dialed Number] = o.[Secondary Ph]
                      OR c.[Dialed Number] = o.[Alternate Ph])
                      AND o.Date = c.Date
GROUP BY c.AgentId, c.Date