#Written by Jen Chen
#Copyrighted by Jen Chen
#CS 122
#Fall 2004
#Lecture: CASE and IF on Mysql.
#Tables in used: All	


select ISBN,Title,CASE PubID 
	when '1' then 'Big House' 			
	when '2' then 'Alpha Press' 
	when '3' then 'Small House' 
	when '4' then 'Another Press' 				
	END AS Publisher 
from BooksJC

================================================================================	

select OrderID, OrderDate, 			
	If(month(shippedDate)='1','Jan',	
	   IF(month(shippedDate)='2','Feb',
	   IF(month(shippedDate)='3','Mar',
	   IF(month(shippedDate)='4','April',	   
	   IF(month(shippedDate)='5','May','Other month')))))	
from ShippingOrders


================================================================================	
	select OrderId,date_format(orderDate,'%W\t%Y-%M-%d') from ShippingOrders;
================================================================================	

select OrderID,	(CASE	   
			WHEN datediff(shippedDate,orderDate) BETWEEN 1 AND 7
			THEN '1 week'	     
			ELSE ' '	   
			END) AS Dates,	
		(CASE WHEN datediff(shippedDate,orderDate) BETWEEN 8 and 14
			THEN '2 weeks'
			ELSE ' '
			END) AS Dates,
		(CASE WHEN datediff(shippedDate,orderDate) BETWEEN 15 and 21
			THEN '3 weeks'
			ELSE ' '
			END) AS Dates,
		(CASE WHEN datediff(shippedDate,orderDate) > 21	     
			THEN '3+ weeks'	     
			ELSE ' '	   
			END) AS Dates
from ShippingOrders


================================================================================
	
	select OrderId, IF(datediff(shippedDate,Orderdate) between 1 and 7,'1 week',
                   IF(datediff(shippedDate,OrderDate) between 8 and 14, '2 weeks',
                   IF(datediff(shippedDate,OrderDate) between 15 and 21, '3 weeks',
                   '3+ weeks'))) AS dates
	from ShippingOrders
	Order By dates