I have two tables - invoices, with such structure:

and Activity:

I need to select invoices from Invoice table only if invoice's date is in at least one of range of related person's session ranges in Activity table.

For example, result based on two data sets:

We will receive:

• invoice #1, because "2022-02-01" is in the range #1 in Activity table
• invoice #3, because "2022-02-05" is in the range #3 in Activity table
• invoice #4, because "2022-02-09" is in the range #4 in Activity table

We won't receive:

• invoice #2, because "2022-02-08" is not in ranges #1, #2 for person 1
• invoice #5, because "2022-02-09" is not in ranges #5 for person 3

Basic query is

SELECT InvoiceID from Invoice invoice
JOIN ON Activity activity on invoice.PersonID = activity.PersonID

How can I create a rule "date is in at least one session range for invoice's person"? I tried GROUP BY with HAVING COUNT(case Date is between SessionStart and SessionDate then 1 end) = 0, but it doesn't work.

CodePudding user response：

An EXISTS filter should work:

SELECT InvoiceID
FROM Invoice invoice
WHERE Exists
(
    SELECT 1
    FROM Activity activity
    WHERE activity.PersonID = invoice.PersonID
    AND invoice.[Date] Between activity.SessionStart And activity.SessionEnd
);