title: SQL query timeout for combining phone data
body: <p>I have a SELECT statement (see below) that I am running and would like to only show the data for numbers that I have in another table.  The query takes too long to run and I am looking for possible feedback on how to optimize:</p>
  
  <pre><code>SELECT Connect.PortName
         , Count(Calls.DialedNumber) AS Dials
         , Count(DISTINCT Calls.DialedNumber) AS Numbers
         , CONVERT(CHAR(8), Dateadd(second, Sum(Datediff(second, Calls.StartTime, Calls.EndTime)), 0), 108) AS CustMinSec
         , CONVERT(VARCHAR, Dateadd(ms, Sum(Connect.TalkTimeSeconds * 1000), 0), 108) AS TalkTime
         , CONVERT(CHAR(8), Dateadd(second, Sum(Datediff(second, 0, Connect.RingTime)), 0), 108) AS RingTime
         , CONVERT(CHAR(8), Dateadd(second, Sum(Datediff(second, 0, Connect.HoldTime)), 0), 108) AS HoldTime
  FROM   Calls
         INNER JOIN Connect ON Calls.ID = Connect.CallTableID
  WHERE  ( CONVERT(CHAR(10), Calls.StartTime, 101) = '10/26/2015' )
     AND ( Calls.CallType = 3 )
     AND ( Connect.GroupID = 14 )
     AND ( Calls.DialedNumber IN (SELECT DISTINCT '+1'
                                                  + Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Upper(PHONE), 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), 'G', ''), 'H', ''), 'I', ''), 'J', ''), 'K', ''), 'L', ''), 'M', ''), 'N', ''), 'O', ''), 'P', ''), 'Q', ''), 'R', ''), 'S', ''), 'T', ''), 'U', ''), 'V', ''), 'W', ''), 'X', ''), 'Y', ''), 'Z', ''), '-', ''), '(', ''), ')', '') AS MAIN
                                  FROM   CUSTTABLE
                                  WHERE  ( DATAAREAID = 'SHA' )) )
  GROUP  BY Connect.PortName
  ORDER  BY Connect.PortName 
  </code></pre>
  
  <p>This only shows the Main number, but I need to search the FAX, Cellular and Additional numbers as well.  The fact that it times out with only looking into the 1 column is a concern.</p>
  
  <p>Any guidance would be appreciated and I might be making this more difficult.  I have another version that does an UNPIVOT for all the columns, but it timed out so I simplified to this.  I am running on SQL 2005 server with SSRS report server front end.</p>