Redirection to a new Aspx Page

This is a quick blog about how you can redirect to another page.

Response.Redirect("~/Path/To/Your/File");

This code is very simple you just call the 'Response' scope and the method "Redirect" and input the path and the file you want to redirect to as a string. And when the code is hit in your site it will redirect to your inputed content page.

SQL-IF and CASE

This time around am going to take a look into the 'if' and 'case' used in SQL... as you can see in the code given below you have a very good feature from SQL. Making decisions inside a query can come in very handy as show below in the given 'if' statements included in this query.

DECLARE @num int
SET @num = 4

DECLARE @salesPersonId int

if @num = 3
BEGIN
 set @salesPersonId = 
 (
  SELECT top 1 SalesPersonID
  FROM Sales.SalesPerson
 )
 SELECT 'The number is 3' [Output]
END
ELSE IF @num = 4
BEGIN
 set @salesPersonId = 4
 SELECT 'The number is 4' [Output]
END
ELSE
BEGIN
 set @salesPersonId = -1
 SELECT 'The number is not valid' [Output]
END


IF @salesPersonId = -1
BEGIN
 SELECT 'No Sales Person selected' SalesPersonId
END
ELSE
BEGIN
 SELECT @salesPersonId SalesPersonId
END

Below this paragraph is a code snippet with a 'case' used to set the 'EmailPromotion' to a more user friendly look, it is actually given as an 'int', by taking in the EmailPromotion and setting it to a string that better ids the kind of promotion that the person is asking for.

SELECT FirstName, LastName,
(
 CASE EmailPromotion
  WHEN 1 THEN 'EmailPromo'
  WHEN 2 THEN 'TextPromo'
  ELSE 'NadaPromo'
 END
) Promo
FROM Person.Contact c

More to Come, CA.

The "UNION" of SQL

"The UNION operator is used to combine the result-set of two or more SELECT statements." Given to us by: http://www.w3schools.com/sql/sql_union.asp.

What this does it take the first "SELECT" statement, and tack on the second "SELECT" statement to the end of it.

SELECT SalesPersonID
FROM Sales.SalesPerson
UNION
SELECT ContactID
FROM HumanResources.Employee

Taking a look at this code snippet we take all of the "SalesPersonID" from "Sales.SalesPerson" and all of the "ContactID" from "HumanResources.Employee" and combine them together, even if there are duplicate records.

There is a "UNION ALL" that can be used in the place of just plain "UNION" that will actually give all the records as single records, giving no duplicated records.

Some Awesome Sub-query Practice

Lets take a look at some SQL again, this is three problems that need to be figured out I will be using the database called AdventureWorks...

The first problem to figure out is this..."Select first name, lastname, and email of employees who are sales people"

SELECT c.FirstName, c.LastName, c.EmailAddress
FROM Person.Contact c
WHERE c.contactID IN
 (
 SELECT e.ContactID
 FROM HumanResources.Employee e
 WHERE e.EmployeeID IN
  (
   SELECT SalesPersonID
   FROM Sales.SalesPerson
  )
 ) 

First I actually found the "SalesPersonID" of all the records in the "Sales.SalesPerson" table, this gives me all the IDs in a single table that I can then use to get all the "SalesPersonID" records that are also the same as the "EmployeeID" in the "HumanResources.Employee" table. From the combination of both of these two tables, the "Sales.SalePerson" and "HumanResources.Employee", I get the "ContactID" from the "HumanResources.Employee" table, after it has been sorted through. That allows me to use the "Employee" table "ContactID" and get all "ContactID" records that are also in the "Person.Contact" table "ContactID" records, getting all the records that fall into this criteria I am able to get the "FirstName", "LastName", and "EmailAddress" of all records sorted out by the sub-queries.

The next problem is..."Get emails of all sales people who have made a sale greater than $150k"

SELECT sp.SalesPersonID, c.EmailAddress
FROM Person.Contact c, Sales.SalesPerson sp 
INNER JOIN HumanResources.Employee e
 ON sp.SalesPersonID = e.EmployeeID
WHERE sp.SalesPersonID IN
 (
  SELECT soh.SalesPersonID
  FROM Sales.SalesOrderHeader soh
  WHERE soh.TotalDue > 150000
 )
AND
 c.ContactID = e.ContactID

This query gets all the information from the "Contact" table, to get the "EmailAddress" of the employees. And by using an "INNER JOIN" to "join" the "SalesPerson" and the "Employee" tables to get all the "Employee" records that are only in the "SalesPerson" table. By using the "SalersPersonID" we are able then use a nested select statement to get only the "SalesPersonID" where that record has a "TotalDue" greater than 150,000. And to finally get the "EmailAddress" we only grab the records where the "Contact" "ContactID" is equal to the "Employee" "ContactID".

Another problem to figure out was..."Query all the products that have not sold and figure out the markup by subtracting the standardcost from the listprice. Order the query by markup descending."

SELECT p.Name, p.ProductNumber, (p.ListPrice - p.StandardCost) Markup
FROM Production.Product p
WHERE p.ProductID NOT IN
 (
  SELECT sod.ProductID
  FROM Sales.SalesOrderDetail sod
 )
ORDER BY Markup DESC

This query starts by taking the "Name", "ProductNumber", "ListPrice", and "StandardCost" of the records in the "Product" table. Since I wanted to get an answer that would list the "Marked up" price of all the product not sold, I pulled from all the products and then used a sub-query to get all product in the "SalesOrderDetail", this actually gives me a list of all the product sold. so using a "NOT IN" "WHERE" I am able to all the products "NOT SOLD". Nice huh...

More To Come, CA.

Example of Sub-Queries

Here is a sub query that has a sub-query in the "SELECT" clause of the main query, and another in the "FROM" Clause

use AdventureWorks

DECLARE @range datetime
set @range = '20030101'

SELECT AVG(T.Total) as Average, 
 (
  SELECT COUNT(*)
  FROM Sales.SalesOrderHeader
  WHERE OrderDate between @range and DATEADD(year,1,@range)
 ) as ORDERCOUNT
FROM ( 
  SELECT SUM(sh.TotalDue) Total, sh.CustomerID
  FROM Sales.SalesOrderHeader sh
  WHERE OrderDate between @range and DATEADD(year,1,@range)
  GROUP BY CustomerID
 ) AS t

Lets take a look at what this query actually does. Starting, I "DECLARE" a "datetime" variable, this is to make it more speedy to set the date I would like to check the year for. The main goal of this query is to show the average total for range of one year for all sales, and the "COUNT" for the year of sales done. What the first sub-query, that is located in the select clause, takes all the records from the "SalesOrderHeader" which is actually all the records from when the company started, then drops all the records that don't fall into the range, that was specified in the "SET" at the top.
The next sub-query is actally in the "FROM" clause, its actually used to make another table that takes all the records from the "SalesOrderHeader" and gets only the records in the range, same as the sub-query above. Then groups the records by there "CustomerID" this makes is so they are unique and then takes those grouped records and finds the "SUM" of the "TotalDue" column of these records. This sub-query used in the "FROM" clause makes it appear as a unique table, listing the "Total" sales of each "CustomerID" by taking this they can find out the average a person spends during the given year.

More To Come, CA.

What is a Subquery?

By the definition "Subquery or Inner query or Nested query is a query in a query."

SELECT studentName, major, studentId
FROM StudentTable
WHERE major IN 
 (SELECT major FROM MajorTable WHERE major = 'Awesome')

SELECT *
FROM (SELECT studentId FROM StudetTable WHERE studentId < 3)

declare @studentName varChar(50)
set @studentName = (SELECT top 1 studentName FROM StudentTable)

SQL-Hotel Reservations Assignment

Well here you go...

use Reservations  
   
 select C.name  
 from dbo.Hotel H  
 left outer join dbo.xrefHotexRoom xHR  
 on H.hotelId = xHR.hotelId  
 LEFT OUTER JOIN dbo.Room R  
 on xHR.roomId = R.roomId  
 left outer join dbo.xrefRoomxCustomer xRC  
 on R.roomId = xRC.roomId  
 left outer join dbo.Customer C  
 on xRC.customerId = C.customerId  
 where R.booked = 1  

select R.roomNumber , H.hotelname  
 from Room R  
 left outer join xrefHotexRoom xHR  
 on R.roomId = xHR.roomId  
 left outer join Hotel H  
 on xHR.hotelId = H.hotelId  
 where R.bed = 1 and R.smoking = 1 and R.fridge = 0 and R.hottub = 0  
 and H.bar = 0 and H.exercise = 0 and H.pets = 0 and H.[pool] = 1 and H.restaurant = 1 and H.wireless = 1  

select H.hotelname, r.roomNumber, rt.name, xHRT.rate  
 from Hotel H  
 left outer join xrefHotexRoom xHR  
 on H.hotelId = xHR.hotelId  
 left outer join Room R  
 on xHR.roomId = R.roomId  
 left outer join dbo.xrefRoomxType xRT  
 on R.roomId = xRT.roomId  
 left outer join dbo.RoomType RT  
 on xRT.typeId = RT.typeId  
 left outer join xrefHotelxRoomType xHRT  
 on xRT.typeId = xHRT.typeId AND H.hotelId = xHRT.hotelId  
 where R.booked = 0  
 order by xHRT.rate 

More to Come, CA.