datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to use the CASE function to create computed column?

Execute the following Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor to demonstrate the creation of computed columns and check constraint in table definition (CREATE TABLE).

/*

SQL CASE function is used to define computed columns (Salutation, Deductions)

in the create table statement.

 

The syntax for a computed column is: ColAlpha AS ....

 

A computed column is updated instantenously after data insert or update.

*/

USE AdventureWorks;

GO

CREATE TABLE PartTimeEmployee (

  PartTimeEmployeeID INT    IDENTITY ( 1 , 1 )

  CONSTRAINT PartTimeEmployeePK PRIMARY KEY,

  FirstName          VARCHAR(50)    NOT NULL,

  LastName           VARCHAR(50)    NOT NULL,

  Gender             CHAR(1)    NOT NULL CHECK (Gender in ('F','M')),

  Married            BIT    NOT NULL,

-- SQL computed column

  Salutation         AS CASE

    when Gender = 'M' then 'Mr.'

    when Gender = 'F' and Married = 0 then 'Miss'

    when Gender = 'F' and Married = 1 then 'Mrs.'

    else ''

  end,

  Salary             SMALLMONEY    NOT NULL,

-- SQL computed column

  Deductions         AS CASE

    When Salary < 20000 then .10 * Salary

    when Salary between 20000 and 49999 then .22 * Salary

    when Salary between 50000 and 69999 then .34 * Salary

    else .40 * Salary

  end,

  EmploymentStart    SMALLDATETIME    NOT NULL

      CONSTRAINT EmploymentStartDF DEFAULT getdate(),

  IsEmploymentEnded  BIT    NOT NULL

      CONSTRAINT IsEmploymentEndedDF DEFAULT 0,

  EmploymentEnd      SMALLDATETIME    NULL

  )

GO

 

INSERT PartTimeEmployee (FirstName, LastName, Gender,

Married, Salary, EmploymentStart)

SELECT 'Roger', 'Vivamore', 'M', 0, 44.00, '2015-02-01'

 

SELECT * FROM PartTimeEmployee

GO

/* Results

 

PartTimeEmployeeID      FirstName   LastName    Gender      Married     Salutation  Salary      Deductions  EmploymentStart   IsEmploymentEnded EmploymentEnd

1     Roger Vivamore    M     0     Mr.   44.00 4.400000    2015-02-01 00:00:00     0      NULL

*/

-- Cleanup

 

DROP TABLE PartTimeEmployee

GO
------------

 

 

------------

-- UDF as Computed Column - computed column based on user-defined function

------------

USE tempdb;

GO

CREATE TABLE ShippingRate (ID int identity(1,1) primary key,

  OrderValueMax money,

  ShippingRate smallmoney,

  ModifiedDate date default (CURRENT_TIMESTAMP));

GO

INSERT ShippingRate(OrderValueMax, ShippingRate) VALUES

(0.0, 0.15),

(100.0, 0.10),

(1000.0, 0.08),

(10000.0, 0.06),

(100000.0, 0.04)

GO

 

 

CREATE FUNCTION ufnShippingFee (@OrderValue money)

RETURNS money

AS

BEGIN

      DECLARE @Fee money

      SELECT @Fee=@OrderValue * min(ShippingRate)

      FROM ShippingRate where @OrderValue > OrderValueMax

      RETURN (@Fee)

END

GO

 

CREATE TABLE Orders (OrderID int identity(1,1) primary key,

  CustomerID int,

  ProductID int,

  Qty int,

  SellUnitPrice money,

  TotalWithShipping AS Qty * SellUnitPrice+

                       dbo.ufnShippingFee (Qty * SellUnitPrice),

  OrderDate datetime default (getdate()));

GO

 

INSERT Orders (CustomerID, ProductID, Qty, SellUnitPrice) VALUES

(1,1,2,30),

(1,1,12,30),

(1,1,12,130),

(2,2,2,30),

(3,12,200,30)

GO

SELECT * FROM Orders ORDER BY OrderID

GO

/*

OrderID CustomerID ProductID Qty SellUnitPrice TotalWithShipping OrderDate
1 1 1 2 30 69 12/7/2010
2 1 1 12 30 396 12/7/2010
3 1 1 12 130 1684.8 12/7/2010
4 2 2 2 30 69 12/7/2010
5 3 12 200 30 6480 12/7/2010

*/ 

 

------------

 

Exam Prep 70-461
Exam 70-461