title: Improve performance on SELECT CASE with Partition Plus COUNT
body: <p>I have revised the code based on suggestions but now performance has dwindled. Any suggestions are welcome</p>
  
  <pre><code>select *
      from (
      SELECT 
           d.ID, 
             d.HeaderId,
          CASE WHEN h.MyType = 'C' THEN
              RANK() OVER (PARTITION BY  l.Work ORDER BY l.Address1 DESC) 
          ELSE
              RANK() OVER (PARTITION BY  l.Home ORDER BY l.Address1 DESC)
          END 
          AS 'RANK',
  
  
          CASE WHEN h.MyType = 'C' THEN
              COUNT(l.Work) OVER (PARTITION BY  l.Work)
          ELSE
              COUNT(l.Home) OVER (PARTITION BY  l.Home)
          END  
          AS 'MAXCOUNT'
  
      FROM schema1.Details AS d 
          JOIN schema1.BatchHeader AS h 
            ON d.HeaderId = h.ID
          JOIN schema2.Details AS l
            ON d.LeadBatchDetailId = l.Id
          LEFT JOIN LDCs AS ldcElec 
            ON l.LDC_Elec = schema3.Code
          LEFT JOIN LDCs AS ldcGas 
            ON l.ldc_gas = ldcGas.Code
          LEFT JOIN schema2.Accounts ag
            ON (l.Work = ag.Phone AND 'G' = ag.Business AND h.MyType = 'C')
            OR (l.Home = ag.Phone AND 'G' = ag.Business AND h.MyType = 'R')
          LEFT JOIN schema2.Accounts ae 
            ON (l.Work = ae.Phone AND 'E' = ae.Business AND h.MyType = 'C')
            OR (l.Home = ae.Phone AND 'E' = ae.Business AND h.MyType = 'R')
          WHERE d.HeaderId = @Id) 
      ) a     
  WHERE [RANK] = [MAXCOUNT] 
  ORDER BY LdcGasName, LdcElecName
  </code></pre>