Advanced Relational Database */ If & Case Statements Assignment */

use AdventureWorks

--1.The boss needs a quick query.  
--All he needs is a query with Sales Order Id and whether the order was online or offline.  
--Select those two columns and display either Offline or Online in the second column

select soh.SalesOrderID, soh.OnlineOrderFlag,
(case soh.OnlineOrderFlag
 when 0 then 'Offline'
 when 1 then 'Online'
End) as Status
from Sales.SalesOrderHeader soh

--2.It's time for bonuses again.  Go through all sales, listing the  firstname and lastname 
--for the salesperson, as well as the sales level.  
--less than a $50,000 sale is the Tin level
--less than a $100,000 sale is the Silver level
--Anything over $100,000 sale is the gold level

select pc.FirstName, pc.LastName, soh.TotalDue,
'Level'= case
 when soh.TotalDue < 50000 then 'Tin Level'
 when soh.TotalDue< 100000 then 'Silver Level'
 when soh.TotalDue > 100000 then 'Gold Level'
End
from Person.Contact pc
inner join Sales.SalesOrderHeader soh on pc.ContactID = soh.ContactID

--3. Using an if statement, you are going to write a query that has two modes.  
--If a variable is set to Sales mode,  it will give you a unique list of all the names of sales people in 2004.  
--If the variable is set to Customer mode, 
--it will give you a unique list of all the names of the customers in 2004.

Declare @SalesMode varchar(50)
set @SalesMode = 'c'

if @SalesMode = 's'
begin
 select distinct pc.FirstName, pc.LastName
 from Sales.SalesOrderHeader soh
 inner join Sales.SalesPerson sp on soh.SalesPersonID = sp.SalesPersonID
 inner join HumanResources.Employee hre on sp.SalesPersonID = hre.EmployeeID
 inner join Person.contact pc on hre.ContactID = pc.ContactID
 where soh.orderdate > '20040101'
end
else if @SalesMode = 'c'
begin 
 select distinct pc.FirstName, pc.LastName
 from Sales.SalesOrderHeader soh
 inner join Person.Contact pc on pc.ContactID = soh.ContactID
 where soh.orderdate between '20040101' and '20050101'
end

select * from Sales.SalesPerson
select * from Sales.SalesOrderHeader
select * from Sales.Customer
select * from Sales.CustomerAddress