How to get a negative rownumber in sql server

Question

I have a set of data with a DateTime, say CalculatedOn what I would like is to get start at the current date getdate() and get an x amount of records from before the current date, and the same amount from after.

If x = 50 then 50 prior to now and 50 in front of now. I was thinking rownumber() would be perfect for this, however I cannot think of how to number the rows negative for prior and positive for future.

Also there is the issue of if there are not 50 prior or future what will happen, but that will come after.

Assume the table has just two 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 is 25/04 12:54

then

Id, SomeTextIWant, CalculatedDate
-- 50 from before now--
-----now here-----
-- 50 from after now--

Answer 1

You can use two CTE's, one for past and one for future dates, then use ROW_NUMBER with ASC and DESC, multiply before now with -1 and concat all:

WITH dataBefore AS
(
    SELECT d.*, rn = (-1) * row_Number() over (Order By CalculatedOn DESC)
    FROM dbo.TableName d
    WHERE CalculatedOn < GetDate()
)
, dataAfter AS
(
    SELECT d.*, rn = row_Number() over (Order By CalculatedOn ASC)
    FROM dbo.TableName d
    WHERE CalculatedOn >= GetDate()
)
SELECT * FROM
(
    SELECT db.*
    FROM dataBefore db
    UNION ALL 
    SELECT da.*
    FROM dataAfter da
)x
WHERE x.rn >= -50 AND x.RN <= 50
ORDER BY x.RN