Select case statement : Case « Select Query « SQL Server / T-SQL

Home
SQL Server / T-SQL
1.Aggregate Functions
2.Analytical Functions
3.Constraints
4.Cursor
5.Data Set
6.Data Type
7.Database
8.Date Timezone
9.Index
10.Insert Delete Update
11.Math Functions
12.Select Query
13.Sequence
14.Store Procedure Function
15.String Functions
16.Subquery
17.System
18.Table
19.Table Joins
20.Transact SQL
21.Transaction
22.Trigger
23.View
24.XML
SQL Server / T-SQL » Select Query » Case 




Select case statement

1create table employee(
2>     ID          int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (10),
7>     region      char (1))
8> GO
1>
2insert into employee (ID, name,    salary, start_date, city,       region)
3>               values (1,  'Jason', 40420,  '02/01/94', 'New York', 'W')
4> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (2,  'Robert',14420,  '01/02/95', 'Vancouver','N')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (3,  'Celia', 24020,  '12/03/96', 'Toronto',  'W')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (4,  'Linda', 40620,  '11/04/97', 'New York', 'N')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (5,  'David', 80026,  '10/05/98', 'Vancouver','W')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (6,  'James', 70060,  '09/06/99', 'Toronto',  'N')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (7,  'Alison',90620,  '08/07/00', 'New York', 'W')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (8,  'Chris', 26020,  '07/08/01', 'Vancouver','N')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (9,  'Mary',  60020,  '06/09/02', 'Toronto',  'W')
3> GO

(rows affected)
1>
2select from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          Jason            40420 1994-02-01 00:00:00.000 New York   W
          Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          Linda            40620 1997-11-04 00:00:00.000 New York   N
          David            80026 1998-10-05 00:00:00.000 Vancouver  W
          James            70060 1999-09-06 00:00:00.000 Toronto    N
          Alison           90620 2000-08-07 00:00:00.000 New York   W
          Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          Mary             60020 2002-06-09 00:00:00.000 Toronto    W

(rows affected)
1>
2> DECLARE @Markup     money
3> DECLARE @Multiplier money
4>
5SELECT @Markup = .10             -- Change the markup here
6SELECT @Multiplier = @Markup + -- We want the end price, not the amount
7>                                  -- of the increase, so add 1
8SELECT ID, Name, salary,
9>    Salary * @Multiplier AS "Marked Up salary""New salary" =
10>    CASE WHEN FLOOR(salary * @Multiplier + .24> FLOOR(salary * @Multiplier)
11>                      THEN FLOOR(salary * @Multiplier.95
12>         WHEN FLOOR(salary * @Multiplier + .5>  FLOOR(salary * @Multiplier)
13>                      THEN FLOOR(salary * @Multiplier.75
14>         ELSE FLOOR(salary * @Multiplier.49
15>   END
16FROM Employee
17> GO
ID          Name       salary      Marked Up salary      New salary
----------- ---------- ----------- --------------------- ----------------------
          Jason            40420            44462.0000             44462.4900
          Robert           14420            15862.0000             15862.4900
          Celia            24020            26422.0000             26422.4900
          Linda            40620            44682.0000             44682.4900
          David            80026            88028.6000             88028.7500
          James            70060            77066.0000             77066.4900
          Alison           90620            99682.0000             99682.4900
          Chris            26020            28622.0000             28622.4900
          Mary             60020            66022.0000             66022.4900

(rows affected)
1>
2> drop table employee
3> GO
1>

           
       














Related examples in the same category
1.Case when and else
2.CASE: return a specified value based on a set of business logic
3.Use CASE in the UPDATE statement
4.'Case when' statement in a procedure
5.Case When with else
6.Case when NULL
7.Case when with Mod and ABS()
8.A simple CASE takes an expression that equates to a Boolean result
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.