/*------------------------------------------------------------------------------------------------------------

--Using_sql_case_statements_for_conditional_logic _0035.sql

In programming, conditional logic is a necessity when one needs to evaluate a condition. Typically these are implemented using the 
if - else – then clause.  However as your search conditions grow, this can lead to many lines of code also causing difficult readability.  
Another option that is available in SQL programming is using select CASE statement.

The Select Case statement allows you to use as many conditions as you need, making it convenient to write concise code for situations 
in which there are many choices. The CASE function is a very useful T-SQL function. With this function you can replace a column 
value with a different value based on the original column value. 

The CASE function allows you to evaluate a column value on a row against multiple criteria, where each criterion might return a 
different value. The first criterion that evaluates to true will be the value returned by the CASE function, as such it can be more 
efficient than using if - else – then clause. In addition Case statements can also be used to implement conditions without columns.

Microsoft SQL Server Books Online documents two different formats for the CASE function. The Syntax looks like this:

CASE input_expression 
    WHEN when_expression THEN result_expression 
        [ ...n ] 
    [ 
        ELSE else_result_expression 
    ] 
END

Where 

the "input_expression" is any valid Microsoft SQL Server expression, 
the "when_expression" is the value in which the input_expression is compared, 
the "result_expression" is the value that will be return for the CASE statement if the "when_expression" evaluates to true, 
the "else_result_expression" is the value that will be returned if no "when_expression" evaluates to true 

Let us start looking at some examples off using case statements.  We are going to be using Northwind database which is a sample 
database from Microsoft SQL server.  More information on this database can be found at this web URL:

http://www.codeproject.com/KB/database/InstallingNorthwindAndPub.aspx


------------------------------------------------------------------------------------------------------------*/


USE NORTHWIND


--1 This example shows you how to you Select Case without any data tables, just to make a conditional decision


DECLARE @MONTH INT
SELECT @MONTH=DATEPART(MONTH, GETDATE()) --gets the month
PRINT @MONTH

SELECT 

	CASE 

	WHEN @MONTH >= 1 AND @MONTH <= 3 THEN 'WINTER'
	WHEN @MONTH >= 4 AND @MONTH <= 6 THEN 'SPRING'
	WHEN @MONTH >= 7 AND @MONTH <= 9 THEN 'SUMMER'
	WHEN @MONTH >= 10 AND @MONTH <= 12 THEN 'FALL'

END

/*

5

------
SPRING

*/


--2 This one looks at the Country column in Employees table and determines Shipping
--Note Shipping is not a column in the table and is generated on the Fly


SELECT 
EMPLOYEEID, 
COUNTRY, 
SHIPPING=CASE COUNTRY 
	WHEN 'USA' THEN 'DOMESTIC'
	WHEN 'UK' THEN 'INTERNATIONAL'
	ELSE 'OTHER'
	END
								
FROM EMPLOYEES

/*

EMPLOYEEID  COUNTRY         SHIPPING
----------- --------------- -------------
1           USA             DOMESTIC
2           USA             DOMESTIC
3           USA             DOMESTIC
4           USA             DOMESTIC
5           UK              INTERNATIONAL
6           UK              INTERNATIONAL
7           UK              INTERNATIONAL
8           USA             DOMESTIC
9           Pakistan        OTHER

*/




--3 Last example could be used in the Inventory department to track level of products in Stock
-- Again note INVENTORY_LEVEL is not a column in the table and is generated on the Fly for maybe a report



SELECT
PRODUCTID,
PRODUCTNAME,
UNITPRICE,
UNITSINSTOCK,
INVENTORY_LEVEL=
	CASE 
		WHEN UNITSINSTOCK < 10 THEN 'LOW'
		WHEN UNITSINSTOCK > 100 THEN 'HIGH'
		ELSE 'OK'
		
	END
FROM PRODUCTS
WHERE CategoryID IN (6,7)

/*

PRODUCTID   PRODUCTNAME                              UNITPRICE             UNITSINSTOCK INVENTORY_LEVEL
----------- ---------------------------------------- --------------------- ------------ ---------------
7           Uncle Bob's Organic Dried Pears          30.00                 15           OK
9           Mishi Kobe Niku                          97.00                 29           OK
14          Tofu                                     23.25                 35           OK
17          Alice Mutton                             39.00                 0            LOW
28          Rössle Sauerkraut                        45.60                 26           OK
29          Thüringer Rostbratwurst                  123.79                0            LOW
51          Manjimup Dried Apples                    53.00                 20           OK
53          Perth Pasties                            32.80                 0            LOW
54          Tourtière                                7.45                  21           OK
55          Pâté chinois                             24.00                 115          HIGH
74          Longlife Tofu                            10.00                 4            LOW

*/







/*
   

Compiled by http://www.learningsqlserver2008.com/, Kash Data Consulting LLC 

Disclaimer:

All sample code and scripts are compiled by Kash Data Consulting LLC for illustrative purposes only. Kash Data Consulting LLC, 
therefore, cannot guarantee or imply reliability, or function of this code or scripts.

All code contained herein are provided to you "AS IS" without any warranties of any kind. Please test all code and 
scripts in test environment before deployment in production systems.

*/