SQL max for previous rows

Question

I have Ticker, Dt, [Open], Volume for the input table

Dt is yyyy-mm-dd

I want to return every row where ticker = 'IBM' and also returns the Ticker, Dt, [Open], Volume fields and then include a max column for only say for the previous x days. Let's say 3 days for the sake of not posting too much data.

But I don't know how to get the time frame correct so that the max is only contained to so many days back.

In Table:

Tck    Dt          [Open]      Volume   
IBM 2010-05-21  122.160000  6881000
IBM 2010-05-24  125.260000  6876000
IBM 2010-05-25  121.470000  9498800
IBM 2010-05-26  124.890000  9085900
IBM 2010-05-27  125.050000  7726500

What I need:

Tck    Dt          [Open]      Volume  Max
IBM 2010-05-21  122.160000  6881000 122.160000
IBM 2010-05-24  125.260000  6876000 125.260000
IBM 2010-05-25  121.470000  9498800 125.260000
IBM 2010-05-26  124.890000  9085900 125.260000
IBM 2010-05-27  125.050000  7726500 125.050000

Here is my current SQL, but obvious doesn't group the Max value correctly.

Select Ticker, 
       Dt, 
       [Open], 
       Volume,
       (Select Max([Open]) from Daily_NYSE
            where Ticker = 'IBM'
            and Dt between DateAdd(Day,-3,'2010-05-27') and '2010-05-27') as 'Max'
from Daily_NYSE
where Ticker = 'IBM'
and Dt between DateAdd(Day,-6,'2010-05-27') and '2010-05-27' 

Thanks! Adam

Answer 1

Do you mean like this?

Edited to work off last 3 trading days

SELECT * INTO #stocks FROM 
(
SELECT 'IBM' AS Ticker, CAST('2010-05-21' AS DATE) AS dt, 122.160000 AS [OPEN] ,12639500 AS Volume UNION ALL
SELECT 'IBM' AS Ticker, CAST('2010-05-24' AS DATE), 125.260000 AS [OPEN] ,6876000 AS Volume UNION ALL
SELECT 'IBM' AS Ticker, CAST('2010-05-25' AS DATE), 121.470000 AS [OPEN] ,9498800 AS Volume UNION ALL
SELECT 'IBM' AS Ticker, CAST('2010-05-26' AS DATE), 124.890000 AS [OPEN] ,9085900 AS Volume UNION ALL
SELECT 'IBM' AS Ticker, CAST('2010-05-27' AS DATE), 125.050000 AS [OPEN] ,7726500 AS Volume 
) X

;WITH NumberedStocks AS
(
SELECT Ticker, dt, [Open], Volume, 
     ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY dt) AS rn
FROM #stocks
)
SELECT ns1.Ticker, ns1.dt, ns1.[Open], ns1.Volume, MAX(ns2.[Open]) AS MaxPrev3
FROM NumberedStocks ns1 LEFT JOIN NumberedStocks ns2
ON ns1.Ticker = ns2.Ticker AND ns2.rn 
   BETWEEN ns1.rn-3 AND ns1.rn-1 /*Or should this be ns1.rn-2 AND ns1.rn?*/
GROUP BY ns1.Ticker, ns1.dt, ns1.[Open], ns1.Volume

Answer 2

I think I just answered my own question:

Select a.Ticker, 
       a.Dt, 
       a.[Open], 
       a.Volume,
       (Select Max([Open]) from Daily_NYSE b
            where Ticker = 'IBM'
            and b.Dt between DateAdd(Day,-2,a.Dt) and a.Dt) as 'Max'
from Daily_NYSE a
where Ticker = 'IBM'
and a.Dt between DateAdd(Day,-12,'2010-05-27') and '2010-05-27' 

Answer 3

Looks like you wanted your Max to be relative to the 3 days previous of the given day? If not, please comment.

DECLARE @SomeDate smalldatetime    
SELECT @SomeDate = '2010-05-27'

SELECT Ticker, 
       Dt, 
       [Open], 
       Volume,
       (SELECT Max([Open]) 
            FROM Daily_NYSE AS D2
            WHERE D2.Ticker  = 'IBM'
            AND D2.Dt BETWEEN DateAdd(Day,-3,D1.Dt) AND D1.Dt
        ) AS 'Max'
FROM    Daily_NYSE AS D1
WHERE   Ticker = 'IBM'
AND     Dt BETWEEN DateAdd(Day,-6,@SomeDate) AND @SomeDate