Select N rows before and N rows after the record

Question

I have a table where some values are stored for months and years.

Example:

Month  |   Year   |  Value
  1    |   2013   |  1.86
  2    |   2013   |  2.25
  3    |   2013   |  2.31
  ...
  3    |   2016   |  1.55
  4    |   2016   |  1.78

Month and Year combination is a complex primary key. It is guaranteed that all values for all past years exist in the table.

User can select specific month and specific year. Let's say user selected 2014 as year and 6 as month, I need to show 15 rows before and 15 rows after the selected combination.

But if there are not enough rows (less than 15) after the selected combination than I need to get more rows before.

Basically all i need is to return 31 rows (always 31 unless there are not enough rows in the entire table) of data where the selected combination will be as close as possible to the center.

What is the proper way to do that?

Currently I'm stuck with this:

;WITH R(N) AS
(
    SELECT 0
    UNION ALL
    SELECT N+1 
    FROM R
    WHERE N < 29
)
SELECT  * FROM MyTable e
LEFT OUTER JOIN (
    SELECT N, MONTH(DATEADD(MONTH,-N,iif(@year != Year(GETDATE()), DATEFROMPARTS(@year, 12, 31) ,GETDATE()))) AS [Month], 
   YEAR(DATEADD(MONTH,-N,iif(@year!= Year(GETDATE()), DATEFROMPARTS(@year, 12, 31) ,GETDATE()))) AS [Year]
FROM R) s
ON s.[Year] = e.[Year] AND s.[Month] = e.[Month]
WHERE s.[N] is not null

This is not really what I want to do, since it just cuts off next year months

Answer 1

I did it that way.

DECLARE @year INT = 2014, @month INT = 6;
WITH TableAux
     AS (SELECT MyTable.Month
              , MyTable.Year
         FROM MyTable
         WHERE MyTable.Year = @year
         AND MyTable.Month = @month)
     SELECT tb1.Month
          , tb1.Year
          , tb1.Value
     FROM
     (
         SELECT TOP 16 MyTable.Month
                     , MyTable.Year
                     , MyTable.Value
         FROM MyTable
         CROSS JOIN TableAux
         WHERE MyTable.Month <= TableAux.Month
         AND MyTable.Year <= TableAux.Year
         ORDER BY MyTable.Month DESC, MyTable.Year DESC
     ) tb1
     UNION ALL
     SELECT tb2.Month
          , tb2.Year
          , tb2.Value
     FROM
     (
         SELECT TOP 15 MyTable.Month
                     , MyTable.Year
                     , MyTable.Value
         FROM MyTable
         CROSS JOIN TableAux
         WHERE MyTable.Month > TableAux.Month
         AND MyTable.Year > TableAux.Year
         ORDER BY MyTable.Month, MyTable.Year
     ) tb2
     ORDER BY Year, Month

Answer 2

Here is what I've done, seems to be working

select * from (
  select top(31) * from MyTable r
  order by ABS(DATEDIFF(month, DATEFROMPARTS(r.Year, r.Month, 1), DATEFROMPARTS(@Year, @Month, 1)))) s
order by Year, Month

Answer 3

Check this out,

DECLARE @iPrevRows int
DECLARE @iPostRows int
DECLARE @Year int = 2016
DECLARE @Month int = 2

SELECT @iPrevRows= Count(*) 
FROM
[GuestBook].[dbo].[tblTest]
where  (year < @Year ) 
  or (year =@Year and month < @Month)

SELECT @iPostRows= count(*)  from
[GuestBook].[dbo].[tblTest]
where  (year > @Year ) 
  or (year =@Year and month > @Month)

if (@iPrevRows > 15) 
    select @iPrevRows =15

if (@iPostRows > 15) 
    select @iPostRows =15

if (@iPrevRows  < 15 )
   select @iPostRows = @iPostRows  + (15-@iPrevRows)
else if (@iPostRows  < 15 )
   select @iPrevRows = @iPrevRows  + (15-@iPostRows)

CREATE TABLE #tempValues
(
 Year int NOT NULL,
 Month int NOT NULL,
 Value  float
)

insert into #tempValues

SELECT top (@iPrevRows) Month, Year, Value 
from
[GuestBook].[dbo].[tblTest]
where  (year < @Year )  
or (year =@Year and month < @Month)
order by 2 desc,1 desc

insert into #tempValues
SELECT Month, Year, Value   
from
[GuestBook].[dbo].[tblTest]
where     (year =@Year and month = @Month)

insert into #tempValues
SELECT top (@iPostRows) Month, Year, Value   
from
[GuestBook].[dbo].[tblTest]
where  (year > @Year ) 
  or (year =@Year and month > @Month)
  order by 2 ,1 

select * from #tempValues
order by 2,1

Answer 4

How about something simple like this:

;WITH CTE AS (
    SELECT Month
        ,Year
        ,Value
        ,ROW_NUMBER() OVER (ORDER BY Year, Month) rn
    FROM MyTable
    )
SELECT Month
    ,Year
    ,Value
FROM CTE 
WHERE rn >= (SELECT rn - 15 FROM MyTable WHERE Year = @Year AND Month = @Month)
    AND rn <= (SELECT rn + 15 FROM MyTable WHERE Year = @Year AND Month = @Month);

I'm sure there's a more efficient way to do it, but this strikes me as the most maintainable way to do it. It should even work when you pick a value close to the first or last records in the table.

I can't tell if you want 31 rows no matter what. At one point it sounds like you do, and at another point it sounds like you don't.

EDIT: Ok, so you do always want 31 rows if available.

Alright, try this:

;WITH CTE AS (
    SELECT Month
        ,Year
        ,Value
        ,ROW_NUMBER() OVER (ORDER BY Year, Month) rn
    FROM MyTable
    ),
CTE_2 AS (
    SELECT TOP (31) Month
        ,Year
        ,Value
    FROM CTE
    ORDER BY ABS(rn - (SELECT rn FROM MyTable WHERE Year = @Year AND Month = @Month)) ASC
    )
SELECT Month
    ,Year
    ,Value
FROM CTE_2
ORDER BY Year, Month;

Basically, you calculate the difference from the target row number, get the first 31 rows there, and then resort them for output.