CODEWITHSUNDEEP
sqlsql-servert-sqlrolling-computation
Chris Kiniry
Chris Kiniry

Reputation: 529

Rolling 52 Week High

I have a SQL Server stocks database and am trying to calculate the rolling 52 week high and low close prices for the stocks.

When using a window function MAX() and trying to partition to the rolling 52 week periods how can I limit the partition to only the rolling 52 weeks (or 1 year time period)?

I believe what I am doing (screenshot) will produce the rolling maximum for the 365 rolling observations. But because markets are closed on weekends and holidays this may not actually be the same as the 52 weeks:

SELECT 
symbol, 
dates, 
[close],

MAX( [close] ) OVER(PARTITION BY symbol 
    ORDER BY dates
    ROWS BETWEEN 364 PRECEDING AND CURRENT ROW) AS [52_week_high]

FROM dbo.adj_daily_prices

WHERE 
dates BETWEEN CAST('2019-01-01' AS DATE) AND CAST('2021-10-15' AS DATE)
AND
symbol = 'CLR'
AND
is_last = 1

ORDER BY
dates DESC

enter image description here

I imagine there is a clever way to do this with a CASE WHEN expression?

Upvotes: 1

Views: 1042

Answers (2)

tinazmu
tinazmu

Reputation: 5139

By 'main query' I was referring to the last-step returning your results. But in any case I now understand that you don't want the solution to be limited to 1 year, so, I believe this one is closer to what you are seeking. No CTEs or calendar tables, but just a subquery as a select column expression. Not limited to a year.

(I don't know what your is_last column does so I just included is_last=1 thinking that it might be marking the most recent row for each symbol & dates)


with
adj_daily_prices as ( -- this simulates your table
    select *
    from (values
          ('CLR', '2021-01-01', 58, 1)
        , ('CLR', '2019-04-05', 70, 1)
        , ('CLR', '2021-01-05', 56, 1)
        , ('CLR', '2021-02-15', 60, 1)
        ) T(symbol, dates, [close], is_last)
)
SELECT 
    symbol, 
    dates, 
    [close],
    (select max([close])
     from adj_daily_prices ftWkh
     where ftWkh.symbol=adp.symbol
     and is_last=1
     and ftWkh.dates between dateadd(week, -52, adp.dates) and adp.dates
     )  AS [52_week_high]
FROM 
    adj_daily_prices adp

WHERE 
    adp.dates BETWEEN CAST('2019-01-01' AS DATE) AND CAST('2021-10-15' AS DATE)
    AND
    symbol = 'CLR'
    AND
    is_last = 1
ORDER BY
adp.dates desc

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

What you describe is not currently possible with analytic functions in Microsoft SQL Server, but it is possible in other databases. See https://modern-sql.com/caniuse/over_range_between_(datetime)

You can alternatively use a join to the same table, with the date criteria:

select    p.symbol, 
          p.dates, 
          p.[close],
          max(h.[close]) as [52_week_high],
          min(h.[close]) as [52_week_low]
from      adj_daily_prices  p
     join adj_daily_prices  h
       on p.symbol = h.symbol
      and h.dates between dateadd(year,-1,p.dates) and p.dates
where     p.dates between '2019-01-01' and '2021-10-15'
      and p.symbol = 'CLR'
group by  p.symbol, 
          p.dates, 
          p.[close]
order by  p.dates desc;

Upvotes: 2

Related Questions