Using CASE in 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 calculated the current total maximum price for each stock and the cumulative drawdownfor each stock for each day: (maximum price - current price) / maximum 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

Now I want to create another column called peak_cnt.

Peak_cntwill have binary output: 1if drawdown= 0 and 0 for anything else.

Here I want to create:

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 the CASE statement work here? I tried several different versions of CASE but had no success. This is the farthest I got 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 for using CASE or any other solution?

Answer 1

, .
case Drawdown ( ):

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

---

CASE WHEN...

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;

Answer 2

, , :

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

, 'Drawdown', , select. "Drawdown" "0" , .

, , - CTE, .

; 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