Using CASE on a Calculated Column in SQL Server 2012

Question

I have a database called temp, with daily stock prices:

Ticker    Date      price
ABC     01/01/13    100.00 
ABC     01/02/13    101.50 
ABC     01/03/13     99.80 
ABC     01/04/13     95.50 
ABC     01/05/13     78.00 
XYZ     01/01/13     11.50 
XYZ     01/02/13     12.10 
XYZ     01/03/13     17.15 
XYZ     01/04/13     14.10 
XYZ     01/05/13     15.55 

I have calculated a running total of the max price for each stock and the cumulative drawdown for each stock for each day: (max price - current price) / max price)

       SELECT t.Ticker,
              t.Date,
              t.price, 
           max(t.price) over (partition by ticker order by date) as max_price,
           (t.price / max(t.price) over (partition by ticker order by date)) - 1       
as Drawdown

       FROM [temp] t;


    Ticker  Date         price  max_price    Drawdown
    ABC     01/01/13     100.00      100.00       0.00000
    ABC     01/02/13     101.50      101.50       0.00000
    ABC     01/03/13      99.80      101.50      -0.01675
    ABC     01/04/13      95.50      101.50      -0.05911
    ABC     01/05/13      78.00      101.50      -0.23153
    XYZ     01/01/13      11.50      11.50        0.00000
    XYZ     01/02/13      12.10      12.10        0.00000
    XYZ     01/03/13      17.15      17.15        0.00000
    XYZ     01/04/13      14.10      17.15       -0.17784
    XYZ     01/05/13      15.55      17.15       -0.09329

I now want to create another column called peak_cnt.

Peak_cnt will have a binary output: 1 if drawdown = 0 and 0 for anything else.

Here's what I want to produce:

Ticker  Date         price      max_price    Drawdown   Peak_cnt
ABC     01/01/13     100.00      100.00       0.00000       1
ABC     01/02/13     101.50      101.50       0.00000       1
ABC     01/03/13      99.80      101.50      -0.01675       0 
ABC     01/04/13      95.50      101.50      -0.05911       0
ABC     01/05/13      78.00      101.50      -0.23153       0 
XYZ     01/01/13      11.50      11.50        0.00000       1
XYZ     01/02/13      12.10      12.10        0.00000       1
XYZ     01/03/13      17.15      17.15        0.00000       1
XYZ     01/04/13      14.10      17.15       -0.17784       0 
XYZ     01/05/13      15.55      17.15       -0.09329       0

Will CASE statement work here? I tried a few different versions of CASE but have not had any success. This is the farthest I've gotten with CASE:

       SELECT t.Ticker,
              t.Date,
              t.price, 
           max(t.price) over (partition by ticker order by date) as max_price,
           (t.price / max(t.price) over (partition by ticker order by date)) - 1 as Drawdown,

           CASE WHEN 'Drawdown' < 0 Then 0
                ELSE 
           END as Peak_cnt

      FROM [temp] t;

Conversion failed when converting the varchar value 'Drawdown' to data type int.

Any suggestions on successfully using CASE or any other solution?

Bribery

Answer 1

No, this will not work.
You need to move a main query to a subquery, and use case expression reffering to the new Drawdown alias in the outer query (one level up):

SELECT x.*,
       CASE WHEN Drawdown <> 0 Then 0
                ELSE 1
       END as Peak_cnt
FROM (
       SELECT t.Ticker,
              t.Date,
              t.price, 
           max(t.price) over (partition by ticker order by date) as max_price,
           (t.price / max(t.price) over (partition by ticker order by date)) - 1 as Drawdown
      FROM [temp] t
) x

---

alternatively you can copy the whole expression to CASE WHEN ... in this way

SELECT t.Ticker,
       t.Date,
       t.price, 
       max(t.price) over (partition by ticker order by date) as max_price,
       (t.price / max(t.price) over (partition by ticker order by date)) - 1 as Drawdown,

       CASE WHEN (t.price / max(t.price) 
                 over (partition by ticker order by date)) - 1 < 0 
            Then 0
            ELSE 1
       END as Peak_cnt

FROM [temp] t;

Lothair

your first query has a comma after as Drawdown,, also, your case should be CASE WHEN Drawdown = 0 THEN 1 ELSE 0 as using WHEN Drawdown < 0 Then 0 this will return 0 for every negative number and 1 with any positive number, which the OP doesn't asks for that. He asks only if is it equal to 0 return 1. Any other number (above or below 0) should return 0, verifying that with OP would make things clearer for him. (don't stand for OP query condition as it might not be what he needed). – Weltanschauung

@Weltanschauung thank you very much for pointing that, I've corrected these mistakes in the answer. – Lothair

Answer 2

The portion of the code where the error is happening is here:

....   CASE WHEN 'Drawdown' < 0 Then 0
                ELSE 
           END as Peak_cnt...

The reason is that you are trying to use the 'Drawdown' reference to point to the column alias that you created in the same select clause. The compiler is reading the same code and trying to compare the word "Drawdown" to the number "0" and can't figure out how because it has not handled the column aliasing yet.

One way to accomplish the calculation that you're looking for is through a sub-query, or through a CTE that would look like the query below.

; with first_query as
    (
  SELECT t.Ticker,
              t.Date,
              t.price, 
           max(t.price) over (partition by ticker order by date) as max_price,
           (t.price / max(t.price) over (partition by ticker order by date)) - 1 as Drawdown
      FROM [temp] t
    )
select a.Ticker
, a.Date
, a.max_price
, a.Drawdown
, case when a.drawdown < 0 then 0 else 1 end as peak_cnt
from first_query as a

Babylonia