Rolling 52 Week High

Question

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

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

Answer 1

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;