finding local maximums and local minimums in SQL

Question

In order to find the max draw down of a stock price versus time graph, you first have to find all local maximums (peaks) and local minimums (valleys) for a given set of prices and days. How would you do this in SQL Server 2005?

edit: There is a brute force way of doing this with cursors: compare the high of the first day to the high of the next day. if the high of the first day is higher than the high of the next day, the high of the first day is a local Max.

effectively, I need to find every point at which the trend of the price graph changes direction.

edit2: I should note that the database table to work from has the following columns:

stockid int
day date
hi int --this is in pennies
low int --also in pennies
so for a given date range, you'll see the same stockid every day for that date range.

Answer 1

OK, step by step here is what I am thinking:

1 - Find all your "peaks" which are max values with LOWER max values the next day:

DECLARE @HiTable (hi int, day date)

INSERT INTO @HiTable
SELECT hi, day
FROM table t1
WHERE EXISTS (
 SELECT t2.hi
 FROM Table t2
 WHERE t1.hi > t2.hi AND t1.day < t2.day and StockID = X)

2 - Find all your "valleys" which are the min values with HIGHER min values the next day:

DECLARE @LowTable (low int, day date)

INSERT INTO @LowTable
SELECT low, day
FROM table t1
WHERE EXISTS (
 SELECT t2.low
 FROM Table t2
 WHERE t1.low < t2.low AND t1.day < t2.day and StockID = X)

3 - Combine these into a table ordered by date with a identity value to keep us in order

DECLARE @TableVar (low int, hi int, day date, autoid int IDENTITY)
INSERT INTO @TableVar
(SELECT low, hi, day
FROM (
 SELECT Low, NULL as 'hi', date FROM @LowTable
 UNION ALL
 SELECT NULL as 'Low', hi, date FROM @HiTable
 )
ORDER BY DATE)

4 - Delete outliers

DELETE FROM @TableVar WHERE AutoID > (SELECT MAX(AutoID) FROM @Table WHERE low IS NULL)
DELETE FROM @TableVar WHERE AutoID < (SELECT MIN(AutoID) FROM @Table WHERE hi IS NULL)

Answer 2

Admitedly not thoroughly tested - but how about using a CTE, and ROWNUMBER() to do this in two steps

1) Identify all the nextsubseqent hi's for each row 2) any row that immediate next row has a subsequent high less than the current row - then current row must be a local max.

or something like that:

begin 
    DECLARE  @highTable as table (high bigint, day date)

    declare @securityid int,
    @start datetime,
    @end datetime

    set @start = '1-1-2010'
    set @end = '2-1-2010'   
    select @securityid = id from security where riccode = 'MSFT.OQ' ;

    with highsandlows_cte as (
        SELECT 
            ROW_NUMBER() over (order by day) i
            , high
            , day
            , (select top 1 day from quotes nextHi where nextHi.high > today.high and nextHi.day >= today.day and nextHi.securityId = today.securityId order by day asc) nextHighestDay

        FROM 
            quotes today
        WHERE 
            today.securityid = @securityid )

    select 
        * 
        , (Coalesce((select 1 from highsandlows_cte t2 where t1.i + 1  = t2.i and t1.nextHighestDay > t2.nextHighestDay),0))  as isHigh
    from 
        highsandlows_cte t1

    order by 
        day
end

---

ok the above is wrong - this appears to be more on track:

begin 
      DECLARE  @highTable as table (high bigint, day date)

      declare @securityid int,
    @start datetime,
    @end datetime

      set @start = '1-1-2010'
      set @end = '2-1-2010'   
      select @securityid = id from security where riccode = 'MSFT.OQ' ;



      with highsandlows_cte as (
            SELECT 
                  ROW_NUMBER() over (order by day) i
                  , high
                  , day
                  , low
            FROM 
                  quote today
            WHERE 
                  today.securityid = @securityid and today.day > convert(varchar(10), @start, 111) and convert(varchar(10), @end, 111) >today.day)



 select
             cur.day
            , cur.high
            , cur.low
        , case when ((cur.high > prv.high or prv.high IS null)and(cur.high > nxt.high or nxt.high is null)) then 1 else 0 end as isLocalMax
        , case when ((cur.low < prv.low or prv.low IS null)and(cur.low < nxt.low or nxt.low is null)) then 1 else 0 end as isLocalMin
  from 
        highsandlows_cte cur left outer join highsandlows_cte nxt
                on  cur.i + 1  = nxt.i
            left outer join highsandlows_cte prv
                on  cur.i - 1  = prv.i
  order by 
        cur.day
end

Get issues with duplicates (highs / lows) though...