Interesting and challenging self join problem SQL Server 2005

Question

I have this table named OrdersToCall

Data types: All bigints, except for date which is a datetime

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

and I have another table named PhoneNumCalled

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

Now my challenge is: I want to count how many Order Num were called and create a table based off the results.

So for example if agent 1234 called all 3 numbers on 1 order that would still only count as 1 order for that agent. The ratio is 1:1. Once a phone number is called then it is counted as 1 order. No matter if all 3 were called, an agent only has to call 1 of phone numbers to get credit for the order.

In less than 3 months time I already have almost 1/2 a million records so try to be as space conscious as possible.

My solution (Which I wish to revise with your help):
I ended up creating a stored procedure which:

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

Now not only does this create a new table, but since each phone number in each order is now its own row the table becomes HUGE and take up to 2 minutes to create.

Then from this table I derive the counts and store those in yet another table.

Answer 1

I think this is what you're looking for:

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

If you want to know how many calls were made on each date, you would have to join on the date also:

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