SQL If/Case statement practice

use AdventureWorks
go

--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 SalesOrderID,
case OnlineOrderFlag
 when 0 then 'offline'
 when 1 then 'online'
 end online
from Sales.SalesOrderHeader SOD


--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 LastName, FirstName,
 case
  when TotalDue < 50000 then 'Tin Level'
  when TotalDue < 100000 then 'Silver Level'
  when TotalDue > 100000 then 'Gold Level'
 end
from Sales.SalesOrderHeader soh
 inner join Person.Contact p
  on soh.ContactID = p.ContactID
  


--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 @mode varchar (150)
set @mode = 'c' 
If @mode = 's'
 begin
 select distinct firstname, lastname
 from person.Contact pc
 inner join HumanResources.Employee hr
 on pc.ContactID = hr.ContactID
 inner join Sales.SalesOrderHeader so
 on hr.EmployeeID = so.contactID
 where so.OrderDate between '20040101' and '20041231'
 end

else if @mode = 'c'
 begin
 select distinct firstname, lastname 
 from Person.Contact pc
 inner join Sales.SalesOrderHeader so
 on pc.ContactID = so.ContactID
 where so.OrderDate between '20040101' and '20041231'
 end 

SQL If and Case statements

If Statements example:

declare @num int
set @num = 4

select @num

if @num = 3 
begin
 select 'yay...its 3'
end
else
begin
 select 'boo...no 3'
end

Case Statements example

use AdventureWorks
go

select FirstName, LastName,
 case EmailPromotion
  when 1 then 'EmailPromo'
  when 2 then 'TextPromo'
  else 'Leave Me Alone'
 end as promo
from Person.Contact

VB notes 2-18-11 Loops and Do Whiles

To count the number of check boxes checked on the form. 3 Check boxes on the form.

Dim nCount as integer = 0
for each element as object in me.controls
     If TypeOf element Is CheckBox Then
          If CType(element, CheckBox).Checked = True Then
               nCount +=1
          End If
     End If   
Next
txtCount.Text = nCount

Dim nControls As Integer = me.Controls.Count
Dim nCurrent As Integer = 0
Dim obj As Object
Do While nCurrent < nControls

     obj = Me.Controls.Item(nCurrent)
     If TypeOf ojb Is CheckBox Then
          If CType(obj, CheckBox).Checked Then
               nCount +=1
          End If
     End If
     nCurrent +=1
     txtCount.Text = nCount
     If nCount > 1 Then 
          Exit Do
Loop