Rolling 52 Week High-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

I think this can be done by using a calendar table. One can be constructed (if your database doesn't have it already) with a CTE (Í have seen examples simply selecting from an existing table known to have more than 366 rows, like sysobjects). The calendar table is then used as the 'left table' of a left join, and the window aggregate is built over this.

with Cal as -- this produces a calendar table for the most recent 366 days
(
     select top 366 [CalDate]=dateadd(day, - row_number() over (order by id ) , cast(getdate() as date))
     from sysobjects 
     order by id
 )
, adj_daily_prices as ( -- this simulates your table
    select *
    from (values
          ('CLR', '2021-01-01', 58, 1)
        , ('CLR', '2021-01-05', 56, 1)
        , ('CLR', '2021-02-15', 60, 1)
        ) T(symbol, dates, [close], is_last)
)
SELECT 
    symbol, 
    dates, 
    [close],

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

FROM 
    Cal as C
    
    left join
    adj_daily_prices adp
    on adp.dates=C.CalDate

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

ORDER BY
C.CalDate desc