Multiple CASE expressions in select, where and group by

Question

I am trying to solve one of the brilliant problems. I would like to use case in sql to optimize the SQL stored procedure.

ProductMetricsas shown below. This table provides data on the sale price and the number of products with the product. Company, sales region, product name, market division.

ProductMetrics table contains 10,000+ rows

ProductMetrics
| Day | CompanyId | RegionId | ProdId | DivId | Quantity | Sale

We have other lookup tables -

Company(CompanyId, CompanyName), 
Region(RegionId, RegionName),
Product(ProdId, ProductName),
Division(DivId, DivisionName) 

The user can get readable statistics from this table using the following query.

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

I want to have a stored procedure that will be higher than generalized statistics for a specific department or product or company, or both. The SP will be returned based on the Parameter request that we pass. getProductMetrics(queryParam, Day, CompanyId, RegionId, ProdId, DivId)The QueryParameter is shown in parentheses below. For instance,

• (C) . .. by CompanyId
• (CR) , . .
• (P)
• (D) .

... QueryParameter - C, R, P, CRP, D, CR, CP, CD. Parameter .

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

... , .

8 IF.

, 8 select. CASE Proc?

-

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

, case- , . QueryParameter 'CR', divionId ProdId null. storedProc (QueryParameter, CompID, RegionId, ProdId, DivId) exec EXEC getProductMetrics('CR',23, 39, '','') // CR requires only CompID and RegionId

EXEC getProductMetrics('CD',23, '', '',100) // CD requires only CompID and DivId

... ?

Answer 1

, , @ByCompany, , . , , SELECT, WHERE GROUP BY (, , ORDER BY). FROM.

- TransactSQL - - .;)

DECLARE @ByCompany BIT = 0;
DECLARE @ByRegion BIT = 0;
[..]
IF @QueryParameter LIKE ('%C%') @ByCompany = 1
IF @QueryParameter LIKE ('%R%') @ByRegion = 1
[..]
Select 
    m.Day as Date,
    CASE @ByCompany WHEN 1 THEN c.CompanyName ELSE 'ALL' END as 'CompanyName',
    CASE @ByRegion  WHEN 1 THEN r.RegionName  ELSE 'ALL' END as 'RegionName',
    [..]
    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
[..]
where m.Day='12-5-2015'
and (c.CompanyId = @company OR @ByCompany = 0)
and (r.Region = @region OR @ByRegion = 0)
[..]
group by 
    Day, 
    CASE @ByCompany WHEN 1 THEN c.CompanyName ELSE 'ALL' END,
    CASE @ByRegion  WHEN 1 THEN r.RegionName  ELSE 'ALL' END,
    [..]

, , , , , .

Answer 2

SELECT m.Day AS Date,
CASE @QueryParameter
      WHEN EXISTS (C, CRP, CP, CR, CD) THEN (select c.CompanyName from Company where c.CompanyId= @company)
      ELSE 'ALL'
    END as 'CompanyName',

    CASE @QueryParameter
      WHEN exists (R, CR) THEN (select r.RegionName from Region r where r.RegionId=@region)
      ELSE 'ALL'
    END as 'RegionName',

    CASE @QueryParameter
      WHEN exists (P, CRP, CP) THEN (select p.ProductName from Product p where p.ProdId =@product)
      ELSE 'ALL'
    END as 'ProductName',
    CASE @QueryParameter
      WHEN exists (D, CD) THEN (select d.DivisionName from  Division where d.DivId = @division)
      ELSE 'ALL'
    END as 'DivisionName',
    SUM(Quantity), SUM(Sale)
    from ProductMetrics m
    where m.Day='12-5-2015'

. .