Select ID from costumers that do not have contacts last 7 days and 2 times in the month
These are the customers that have not received an e-mail in the last 7 days AND have received less than two e-mails this month:

SELECT *
FROM customer AS c
WHERE c.ID NOT IN (
    SELECT ID        --- the customer ID
    FROM c_contact
    WHERE contdate&gt;=DATEADD(day, -31, SYSDATETIME()) -- for optimization
    GROUP BY ID
    HAVING MAX(contdate)&gt;=DATEADD(day, -7, SYSDATETIME()) OR
           SUM((CASE WHEN contdate&gt;=DATEFROMPARTS(
               YEAR(SYSDATETIME()),
               MONTH(SYSDATETIME()),
               1) THEN 1 ELSE 0 END))&gt;=2);


Conversely, here are customers that have receive an e-mail in the last 7 days OR have received two or more e-mails this month:

SELECT *
FROM customer AS c
WHERE c.ID IN (
    SELECT ID        --- the customer ID
    FROM c_contact
    WHERE contdate&gt;=DATEADD(day, -31, SYSDATETIME()) -- for optimization
    GROUP BY ID
    HAVING MAX(contdate)&gt;=DATEADD(day, -7, SYSDATETIME()) OR
           SUM((CASE WHEN contdate&gt;=DATEFROMPARTS(
               YEAR(SYSDATETIME()),
               MONTH(SYSDATETIME()),
               1) THEN 1 ELSE 0 END))&gt;=2);


Because I've put the two conditions in a single HAVING clause, the two queries only scan the c_contact table once, making them much more efficient. In particular, an optimal index for this type of query would probably look something like this:

CREATE INDEX indexName ON c_contact (ID, contdate);