denali

gistfile1.sql

declare @t table (Id int, Total int)
declare @i int = 1

while @i < 100
begin
insert @t values (@i, @i * 10)
set @i = @i + 1
end

select *, 
	cast(format(sum(Total) over(order by Id), '000.00') as varchar(10)) [Running Total], 
	sum(Total) over(partition by [Group] order by Id) [Running Total (Group)], 
	first_value(Id) over(partition by [Group] order by [Id]) [First],
	last_value(Id) over(partition by [Group] order by [Id]) [Last],
	lag(Id, 11, -9999999) over (order by [Id]) [11 before],
	lead(Id, 11, -9999999) over (order by [Id]) [11 after]
from (select *, IIF(Id % 2 = 0, 'Even', 'Odd') [Group] from @t) as X
order by Id
offset 10 rows
fetch first 5 rows only

/*
Id          Total       Group Running Total Running Total (Group) First       Last        11 before   11 after
----------- ----------- ----- ------------- --------------------- ----------- ----------- ----------- -----------
11          110         Odd   660.00        360                   1           11          -9999999    22
12          120         Even  780.00        420                   2           12          1           23
13          130         Odd   910.00        490                   1           13          2           24
14          140         Even  1050.00       560                   2           14          3           25
15          150         Odd   1200.00       640                   1           15          4           26
*/