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

you may want to check this http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server — Gonzalo.-, Apr 19 '16 at 14:48
My first [SWAG](https://en.wikipedia.org/wiki/Scientific_wild-ass_guess) would be to get the MIN and MAX dates from the table (Fast with an index.) and calculate the appropriate dates to be returned considering the edge cases. Then run a query for the appropriate rows. — HABO, Apr 19 '16 at 15:16

Bacon Bits · Answer 1 · 2016-04-19T15:24:47.433

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.

this is not what I need since if the queried row (@Year and @Month) is the last one it will only return 15 additional records before it. In that case I need to return 30 records before it. Basically if there are less than 15 rows after the queried row i need to get additional rows before it. — Coffka, Apr 19 '16 at 15:18
You already are assummig a row number in your subquery which isnt there. — Mihai, Apr 19 '16 at 15:27
@Mihai How do you figure? The row number is calculated by the first CTE with the ROW_NUMBER() function. The second query, CTE_2, refers to the first CTE, and the final query refers to the second. — Bacon Bits, Apr 19 '16 at 15:32

score 0 · Answer 2 · answered Apr 19 '16 at 16:12

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

score 0 · Answer 3 · answered Apr 19 '16 at 16:16

0

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

answered Apr 19 '16 at 16:16

Coffka

749
1
8
23

Unfortunately that query is not [SARGABLE](https://en.wikipedia.org/wiki/Sargable) due to the functions applied to each row, so the performance will suffer as the table grows. – HABO Apr 19 '16 at 20:22

score 0 · Answer 4 · answered Mar 20 '20 at 16:23

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

Select N rows before and N rows after the record

4 Answers4