CASE Statement SQL Server 2012

Question

Using pubs db I have created the following with UNION ALL, but was trying to do the same with a CASE stmt.

SELECT 
t.title_id AS 'Title ID',
t.ytd_sales 'YTD Sales',
t.price AS 'Original Price',
          'New Price' = CASE t.ytd_sales
           WHEN (t.ytd_sales < 2500.00) THEN CONVERT(DECIMAL(9,2),ROUND   (t.price*1.15,2))
           WHEN (t.ytd_sales BETWEEN 2500.00 AND 10000.00) THEN CONVERT(DECIMAL(9,2),ROUND(t.price*1.10,2))
           WHEN (t.ytd_sales > 10000.00) THEN CONVERT(DECIMAL(9,2),ROUND(t.price*1.05,2))
           ELSE CONVERT(DECIMAL(9,2),ROUND(t.price*1.00,2))
        END

    FROM titles t  
    ;

It does not like to comparison/special operators. is this even possible to do as a CASE stmt? Thanks

possible duplicate of [SQL Server CASE .. WHEN .. IN statement](http://stackoverflow.com/questions/6042767/sql-server-case-when-in-statement) — Martin Smith, May 31 '15 at 13:59

score 2 · Answer 1 · edited May 23 '17 at 12:14

Syntactically, as Martin Smith indicates, this question is a duplicate of SQL Server CASE .. WHEN .. expression.

The proper syntax for this query would be

SELECT 
t.title_id  AS [Title ID],
t.ytd_sales AS [YTD Sales],
t.price     AS [Original Price],
CASE  
       WHEN (t.ytd_sales < 2500.00) THEN CONVERT(DECIMAL(9,2),ROUND   (t.price*1.15,2))
       WHEN (t.ytd_sales BETWEEN 2500.00 AND 10000.00) THEN CONVERT(DECIMAL(9,2),ROUND(t.price*1.10,2))
       WHEN (t.ytd_sales > 10000.00) THEN CONVERT(DECIMAL(9,2),ROUND(t.price*1.05,2))
       ELSE CONVERT(DECIMAL(9,2),ROUND(t.price*1.00,2))
    END     AS [New Price]

FROM dbo.titles t  
;

At the risk of being preachy: column aliases should be in square brackets (as [Title ID]), not single quotes (that are for string literals). Ref: TSQL SELECT Clause

Thank you SO much, Stan - appreciate any and all the direction I can get as I am new to this stuff. Appreciate your patience! — Beta, Jun 01 '15 at 15:58
Follow up - of course your tweak worked like a charm. Thanks again...I now see all the mistakes I made! — Beta, Jun 01 '15 at 22:04

score 0 · Answer 2 · answered May 31 '15 at 23:02

It looks like you are trying to mix up two styles of CASE statements in SQL.

The syntax for the CASE statement in SQL Server (Transact-SQL) is:

Scenario 1: When you have a simple logic where expression will be compared to each values provided:

CASE expression

   WHEN value_1 THEN result_1
   WHEN value_2 THEN result_2
   ...
   WHEN value_n THEN result_n

   ELSE result

END

OR

Scenario 1: When you have more complex logic where you have different conditions to evaluate:

CASE

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

Courtesy:SQL Server: Case Statement

CASE Statement SQL Server 2012

2 Answers2

Linked