Select where result with additional row above or below

Question

This is the sample table

SELECT NoPolisi, ComLocID, Jenis, TglValid, rn 
FROM  (
          SELECT NoPolisi, ComLocID, Jenis, TglValid, NoPolisiBaru,
                 ROW_NUMBER() OVER (PARTITION BY NoPolisi ORDER BY TglValid) rn 
          FROM MstData.dbo.Ms_UbahBentukArmada
      ) A
WHERE NoPolisi = 'BK 8819 CF'

And here is the select result query with where tglValid between 2 dates

DECLARE 
    @DateFrom smalldatetime = '20170101',
    @DateTo smalldatetime = '20171201'
SELECT NoPolisi, ComLocID, Jenis, TglValid, rn 
FROM  (
          SELECT NoPolisi, ComLocID, Jenis, TglValid, NoPolisiBaru, 
                 ROW_NUMBER() OVER (PARTITION BY NoPolisi ORDER BY TglValid) rn 
          FROM MstData.dbo.Ms_UbahBentukArmada
      ) A
WHERE NoPolisi = 'BK 8819 CF' 
AND   TglValid BETWEEN @DateFrom AND @DateTo

The result of query above is

My Question is, Can i get The selected Result have an additional row above or below so the result have include row rn=5 or include row rn=2 ?

edit: in the @gotqn answer it used

DECLARE @DataSource TABLE
(
     [id] INT
    ,[date] DATETIME2(0)
);

INSERT INTO @DataSource ([id], [date])
VALUES (100, '2000-01-01')
      ,(110, '2016-03-01')
      ,(120, '2017-06-06')
      ,(130, '2017-07-01')
      ,(140, '2018-01-01');

DECLARE 
    @DateFrom smalldatetime = '20170401',
    @DateTo smalldatetime = '20170430';

    WITH DataSource AS
(
    SELECT * 
          ,ROW_NUMBER() OVER (ORDER BY [date]) AS [rn]
          ,IIF
          (
            [date] BETWEEN @DateFrom AND @DateTo
            OR
            LAG([date]) OVER(ORDER BY [date]) BETWEEN @DateFrom AND @DateTo
            ,1
            ,0
           ) AS [in_interval]
    FROM @DataSource
)
SELECT *
FROM DataSource
-- WHERE [in_interval] = 1;

but if the date range is in the middle like the code above it must return the row 2 :

id      date                rn      in_interval
100     2000-01-01 00:00:00 1       0
110     2016-03-01 00:00:00 2       1
120     2017-06-06 00:00:00 3       0
130     2017-07-01 00:00:00 4       0
140     2018-01-01 00:00:00 5       0

Thanks For Helps.

Answer 1

Thanks for all answer. the result i want are like this

DECLARE @DataSource TABLE
(
     [id] INT
    ,[date] DATETIME2(0)
);

INSERT INTO @DataSource ([id], [date])
VALUES (100, '2000-01-01')
      ,(100, '2016-03-01')
      ,(100, '2017-06-06')
      ,(100, '2017-07-01')
      ,(100, '2018-01-01');

DECLARE 
    @DateFrom smalldatetime = '20170401',
    @DateTo smalldatetime = '20180101';


SELECT * FROM @DataSource WHEre date between @DateFrom AND @DateTo
UNION ALL 
SELECT id, max(date) date FROM @DataSource where date < @DateFrom group by id

Thanks @gotqn for the time to help

Answer 2

So, you need to be able to get:

• the biggest rn smaller then your minimum output data rn
• the smallest rn bigger then your maximum output data rn

As the rn is calculating in the SELECT phase in order to use it we need to materialized the results. Then, using UNION ALL add the desired records using WHERE [rn] = (SELECT MIN(rn) - 1 FROM data) or WHERE [rn] = (SELECT max(rn) + 1 FROM data).

Below, I am using another approach using self join. So, this query:

DECLARE @DataSource TABLE
(
     [id] INT
    ,[date] DATETIME2(0)
);

INSERT INTO @DataSource ([id], [date])
VALUES (100, '2000-01-01')
      ,(110, '2016-03-01')
      ,(120, '2017-06-06')
      ,(130, '2017-07-01')
      ,(140, '2018-01-01');

DECLARE 
    @DateFrom smalldatetime = '20170101',
    @DateTo smalldatetime = '20171201';

WITH DataSource AS
(
    SELECT * 
          ,ROW_NUMBER() OVER (ORDER BY [date]) AS [rn]
          ,CASE WHEN [date] BETWEEN @DateFrom AND @DateTo THEN 1 ELSE 0 END AS [in_interval]
    FROM @DataSource
)
SELECT *
FROM DataSource DS1
LEFT JOIN DataSource DS2
    ON DS1.[rn] = DS2.[rn] - 1;

So, you need to add WHERE DS1.[in_interval] = 1 OR DS2.[in_interval] = 1. In order to get the next row, change the ON clause from this:

ON DS1.[rn] = DS2.[rn] - 1

to this:

ON DS1.[rn] = DS2.[rn] + 1

or add other self join to get the two rows.

---

For, SQL Server 2012+, you can use LEAD and LAG function to calculated if row should be included:

WITH DataSource AS
(
    SELECT * 
          ,ROW_NUMBER() OVER (ORDER BY [date]) AS [rn]
          ,IIF
          (
            [date] BETWEEN @DateFrom AND @DateTo
            OR
            LAG([date]) OVER(ORDER BY [date]) BETWEEN @DateFrom AND @DateTo
            ,1
            ,0
           ) AS [in_interval]
    FROM @DataSource
)
SELECT *
FROM DataSource
-- WHERE [in_interval] = 1;

---

We need to add additional column to count how many rows are in the range. If none of rows is in the range, we are extracting the one row using UNION ALL:

  WITH DataSource AS
(
    SELECT * 
          ,ROW_NUMBER() OVER (ORDER BY [date]) AS [rn]
          ,IIF
          (
            [date] BETWEEN @DateFrom AND @DateTo
            OR
            LAG([date]) OVER(ORDER BY [date]) BETWEEN @DateFrom AND @DateTo
            ,1
            ,0
           ) AS [in_interval]
         ,SUM(IIF( [date] BETWEEN @DateFrom AND @DateTo, 1, 0)) OVER() valid_dates 
    FROM @DataSource
)
SELECT *
FROM DataSource
WHERE [in_interval] = 1
UNION ALL
SELECT *
FROM
(
    SELECT TOP 1 *
    FROM DataSource
    WHERE [date] < @DateFrom
        AND [valid_dates] = 0
    ORDER BY [rn] DESC
) DS