Search code examples
sql-serverssms

SQL - Problem finding records in a range for multiple dates

i am looking for a way to extract data with a lot of possible dates.

example list of dates:

declare @visitDate table (Id DATE)
insert into @visitDate values ('2009-04-02'), ('2019-04-08'), ('2020-01-23')

The records can be from 7 days before up to 7 days after the listed dates. I tried doing it like:

 WHERE effectiveDateTime_date BETWEEN DATEADD(day, -7, '2009-04-02') AND DATEADD(day, +7, '2009-04-02')

Which works for a set date, but i can't seem to figure it out with a long list of dates to check. I have over 2000 dates, so i'd really prefer not them out in "or" statements.

Solution

  • You can use join or exists:

    where exists (select 1
              from @visitDate vd
              where t.effectiveDateTime_date >= dateadd(day, -7, vd.id) and
                    t.effectiveDateTime_date <= dateadd(day, 7, vd.id)
             )