Here is a little "first coffee in the morning" solution:doze:

It uses two instances of a Tally CTE to generate a set N{N+0,,,,N+x} for framing the x rows preceding and current row (kind of).

😎

DECLARE @SET_SIZE INT = 10;

--SET STATISTICS IO ON;

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS (SELECT TOP(SELECT COUNT(*) FROM #dat) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM T T1,T T2,T T3,T T4,T T5)

,NM10(N) AS (SELECT TOP (@SET_SIZE) N - 1 FROM NUMS)

,CALC_SET AS

(

SELECT

NM.N

,NM.N + N10.N AS NX

FROM NUMS NM

OUTER APPLY NM10 N10

)

SELECT

CS.N

,MAX(DT.Value) AS MAX_VAL

FROM CALC_SET CS

INNER JOIN #dat DT

ON CS.NX = DT.RID

GROUP BY CS.N

--ORDER BY CS.N

--SET STATISTICS IO OFF;

IO statistics :pinch:

Table 'Worktable'. Scan count 1, logical reads 20438, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#dat________________________________________________________________________________________________________________000000000023'. Scan count 3, logical reads 114, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.