How to get a negative rownumber in sql server -

i have set of data datetime, calculatedon start @ current date getdate() , x amount of records before current date, , same amount after.

if x = 50 50 prior , 50 in front of now. thinking rownumber() perfect this, cannot think of how number rows negative prior , positive future.

also there issue of if there not 50 prior or future happen, come after.

assume table has 2 columns :

create table mytable (    id int not null constraint pk_mytable primary key,    sometextiwant nvarchar(50) not null,    calculatedate datetime not null ); 

results :

if today 25/04 12:54

then

id, sometextiwant, calculateddate -- 50 before now-- -----now here----- -- 50 after now-- 

you can use 2 cte's, 1 past , 1 future dates, use row_number asc , desc, multiply before -1 , concat all:

with databefore (     select d.*, rn = (-1) * row_number() on (order calculatedon desc)     dbo.tablename d     calculatedon < getdate() ) , dataafter (     select d.*, rn = row_number() on (order calculatedon asc)     dbo.tablename d     calculatedon >= getdate() ) select * (     select db.*     databefore db     union      select da.*     dataafter da )x x.rn >= -50 , x.rn <= 50 order x.rn