Find preceding and succeeding values for a time range in Sql Server for multiple ids

Question

Hello fellow programmers,

I have a sql query where I’m fetching data within a specific range, defined by from, to and an Identifier for a single measurement, id. In addition to the values within the range, I also fetch 10 elements preceding this range, and 10 elements succeeding this range.

I do this by deploying the query in listing [1] and make use of my clustered index on MEASUREMENT_ID, VALUEDATE.

This works quite well for fetching values for a single measurementId, however I want to extend my approach to get values for multiple measurements at once, also with the additional preceding and succeeding elements.

Could anyone please give me a hint on how I might perform such a query.

Also I'm not too happy with performing multiple selects and combining them with UNION ALL, so please feel free to suggest any other way on how to best mount my query.

King Regards.

[1]
--element pre Range...
SELECT * FROM (
    SELECT TOP 10 * FROM TB_VALUES
    WHERE MEASUREMENT_ID = @ID 
    AND VALUEDATE < @DATE_FROM
    ORDER BY MEASUREMENT_ID, VALUEDATE DESC
) as pre 

UNION ALL

SELECT * FROM (
    SELECT * FROM TB_VALUES
    WHERE MEASUREMENT_ID = @ID 
    AND VALUEDATE >= @DATE_FROM
    AND VALUEDATE < @DATE_TO
) as main 

UNION ALL

--element post Range...
SELECT * FROM (
    SELECT TOP 10 * FROM TB_VALUES
    WHERE MEASUREMENT_ID = @ID 
    AND VALUEDATE >= @DATE_TO
    ORDER BY MEASUREMENT_ID, VALUEDATE
) as post 

ORDER BY MEASUREMENT_ID, VALUEDATE

question from:https://stackoverflow.com/questions/66065769/find-preceding-and-succeeding-values-for-a-time-range-in-sql-server-for-multiple

Answer 1

easiest way using your current query would be to include a row_number() partitioned by Id and only select the top 10 that way for each Id.

select  *
from    (   select  *
                    , row_number() over (partition by MEASUREMENT_ID order by VALUEDATE desc) as rn
            from    TB_VALUES
            where   VALUEDATE < @DATE_FROM) as pre
where   rn <= 10

Do this for the succeeding query as well except order by valuedate asc. And remove your ID filters