-- THE CASE STATEMENT --

--Syntax
/*
CASE column_name / No Column Name
WHEN Condition1 / Column Name 
WHEN Condition2 THEN Result1  
WHEN Condition3 THEN Result2  
ELSE Result (Optional)
END;
*/

--1--
select * from products

SELECT productname, categoryid,
  CASE categoryid
   WHEN 3 THEN 'Category is 3'
   ELSE 'Category is not 3'
   END AS cat_col
FROM products
ORDER BY categoryid

--2--
select * from products

SELECT productname, categoryid, unitprice,
  CASE categoryid
    WHEN 1 THEN unitprice*1.1
    WHEN 3 THEN unitprice*1.2
    ELSE unitprice
  END new_price
FROM products
ORDER BY categoryid

--3--
select * from [Order Details]

SELECT OrderID, Discount,
CASE Discount
WHEN 0 THEN 'No Discount'
WHEN 0.05 THEN 'Small Discount'
ELSE 'Fine Discount'
     END 'Discount properties'
FROM [Order Details]

--4--
select * from Orders

SELECT CustomerID, Requireddate, Shippeddate,
	CASE 
	WHEN ShippedDate IS NULL  
	THEN 'Call for schedule shipping'
	WHEN DATEDIFF(dd,ShippedDate,RequiredDate) < 0 
	THEN 'Call and apologize'
	ELSE 'Shipped O.K'
END AS 'Shipping Status',
DATEDIFF(dd,ShippedDate,RequiredDate) ShipChk
FROM Orders 

-- Complax Case
select *, (ship1 + ship2 + ship3) as sumall
from 
(select Year(orderdate) Year, EmployeeID, SUM(Freight) all1, COUNT(*) countall,
SUM(case when ShipVia = 1 then Freight else 0 end)ship1,
COUNT(case when shipvia = 1 then Freight end) count1,
SUM(case when ShipVia = 2 then Freight else 0 end)ship2,
COUNT(case when shipvia = 2 then Freight end) count2,
SUM(case when ShipVia = 3 then Freight else 0 end)ship3,
COUNT(case when shipvia = 3 then Freight end) count3
from orders 
where year(OrderDate) = 1996
group by Year(orderdate), EmployeeID) tmp