How can I get both the rows that fit into a date range, and the single most recent row prior to the-CodePudding

I want to provide a query a date range, for example 11/01/2021 - 11/30/2021. From there I want to get all relevant rows that fall within that date range, which is pretty straightforward - just a simple select with a where clause based on the to and from dates. But after this, I want to get the most recent row (if it exists) prior to the date range, only for the foreign key IDs that appear within the date range.

So as an example,

ID	FK ID	Date
1	101	09/05/2021
2	101	10/29/2021
3	101	11/05/2021
4	201	11/20/2021
5	301	08/13/2021
6	401	11/01/2021
7	401	11/23/2021

If I were to input a date range of 11/01/2021 - 11/30/2021, I would expect the following results

ID	FK ID	Date
2	101	10/29/2021
3	101	11/05/2021
4	201	11/20/2021
6	401	11/01/2021
7	401	11/23/2021

My first thought was a UNION, where the second query grabs the max date prior to the date range and unions with the first query which has all the values in the given date range. But I think this would fail with values like ID 5 in the table above - because there's no restriction on the second query that it would need the same FK ID to exist in the first table. Maybe I'm just not thinking straight but I can't think of any way to restrict a union in this way?

CodePudding user response：

You can use a cte to select rows within the range and add more rows relative to this cte

with cte as (
  SELECT *
  FROM tb
  WHERE [Date] >= '11/1/2021' AND [Date] <= '11/30/2021'
)
select * 
from cte
union all
select *
from (
  select top(1) with ties *
  from tb
  where exists (select 1 from cte where cte.fk_id = tb.fk_id)
     and [Date] < '11/1/2021'
  order by row_number() over(partition by fk_id order by [date] desc)
) t
order by id;

db<>fiddle

CodePudding user response：

The following query produces the output you want

WITH cte AS(
  SELECT ID,FK_ID,[DATE]
  FROM tb
  WHERE [DATe] >= '11/1/2021' AND [Date] <= '11/30/2021')
SELECT * 
FROM cte    
UNION ALL  
SELECT ID,FK_ID,[DATE]
FROM
    (SELECT *,
       CASE WHEN LEAD([Date]) over(ORDER BY ID) >= '11/1/2021' AND LEAD([Date]) over(ORDER BY ID) <= '11/30/2021' THEN 1 ELSE 0 END AS rnk
     FROM tb) t
  WHERE rnk = 1 AND [DATe] < '11/1/2021' AND FK_ID IN (SELECT FK_ID FROM cte)
ORDER BY ID

demo in db<>fiddle