title: Multiple CASE expressions in select, where and group by clause
body: <p>I am trying to solve one of the brilliant problems. I would like to use case in sql to optimize the SQL stored procedure.</p>
  
  <p><code>ProductMetrics</code> table as below. This table has data about sale price and quantity of products along with product Company, Region of sale, Product Name, Market division. </p>
  
  <p>Table ProductMetrics contains 10000+ rows</p>
  
  <pre><code>ProductMetrics
  | Day | CompanyId | RegionId | ProdId | DivId | Quantity | Sale
  </code></pre>
  
  <p>We have other reference tables - </p>
  
  <pre><code>Company(CompanyId, CompanyName), 
  Region(RegionId, RegionName),
  Product(ProdId, ProductName),
  Division(DivId, DivisionName) 
  </code></pre>
  
  <p>User can get readable statistics from this table using below query.</p>
  
  <pre><code>Select m.Day, c.CompanyName, r.RegionName, p.ProductName, d.DivisionName, m.Quantity, m.Sale
  from ProductMetrics m
  left outer join Company on c.CompanyId = m.CompanyId
  left outer join Region on r.RegionId = m.RegionId
  left outer join Product on p.ProdId = m.ProdId
  left outer join Division on d.DivId = m.DivId
  where m.Day = '12-05-2015' and
        m.CompanyId= 15 and
        m.RegionId =10
  </code></pre>
  
  <p>I wish to have stored procedure that will get above summarized stats for particular division, or product or company or for both. SP will return based on queryParameter we pass. <code>getProductMetrics(queryParam, Day, CompanyId, RegionId, ProdId, DivId)</code> QueryParameter is given in bracket in below examples. 
  For example, </p>
  
  <ol>
  <li>(C) Get total quantity and sale for a particular Company. i.e. group by CompanyId</li>
  <li>(CR)Total quantity and sale grouped by Company and Region. Different combinations between Company and Region.</li>
  <li>(P) Total quantity and sale grouped by Company</li>
  <li>(D) Get total quantity and sale for a particular division.</li>
  </ol>
  
  <p>and so on...
  QueryParameter could be of value - <code>C, R, P, CRP, D, CR, CP, CD</code>. For each queryParameter, I have select statements. For example below</p>
  
  <pre><code>IF @queryParameter IN ('C')
    select m.Day, c.CompanyName, 'ALL' as Region, 'ALL' as ProductName, 'ALL' as DivisionName, SUM(m.Quantity), SUM(m.Sale)
      from ProductMetrics m
      left outer join Company on c.CompanyId = m.CompanyId
      where m.Day = '12-05-2015' and
          c.CompanyId =23
      group by m.Day, c.CompanyName
  </code></pre>
  
  <p>So on..
  Above Stored procedure gives me total sale for a particular Product and day from all available division, region.</p>
  
  <p>To stored procedure, I need to write 8 select statments with IF condition. </p>
  
  <p>Question is Rather than 8 different select statement, Should I use CASE statements to have only single storedProc ?</p>
  
  <p>I tried below-</p>
  
  <pre><code>Select m.Day as Date,
         CASE @QueryParameter
            WHEN IN (C, CRP, CP, CR, CD) THEN c.CompanyName
            ELSE 'ALL'
          END as 'CompanyName',
  
          CASE @QueryParameter
            WHEN IN (R, CR) THEN r.RegionName
            ELSE 'ALL'
          END as 'RegionName',
  
          CASE @QueryParameter
            WHEN IN (P, CRP, CP) THEN p.ProductName
            ELSE 'ALL'
          END as 'ProductName',
  
          CASE @QueryParameter
            WHEN IN (D, CD) THEN d.DivisionName
            ELSE 'ALL'
          END as 'DivisionName',
  
          SUM(Quantity), SUM(Sale)
  from ProductMetrics m,
  left outer join Company on c.CompanyId = m.CompanyId
  left outer join Region on r.RegionId = m.RegionId
  left outer join Product on p.ProdId = m.ProdId
  left outer join Division on d.DivId = m.DivId
  
  where m.Day='12-5-2015' and
        r.Region = @region and
        p.ProdId = @product
        c.CompanyId = @company and
        d.DivId = @division
  group by Day, CompanyName,RegionName, ProductName, DivisionName
  </code></pre>
  
  <p>As you know, case statements should also be present for join, where and group by statements. becuase for QueryParameter 'CR', divionId and ProdId will passed as null. 
  I will run storedProc(QueryParameter, CompID, RegionId, ProdId, DivId) with below exec
  <code>EXEC getProductMetrics('CR',23, 39, '','') // CR requires only CompID and</code> RegionId</p>
  
  <pre><code>EXEC getProductMetrics('CD',23, '', '',100) // CD requires only CompID and DivId
  </code></pre>
  
  <p>and so on..
  Could you assist me in getting this optimized query?</p>