Chaining CTEs

Use Common Table Expressions (CTEs) instead of nested subqueries:

;with OrderCompanies as (
	select distinct o.clientid 
	from orders o
	inner join orderitems oi on o.orderid = oi.orderid
	where o.confirmed > '2025-1-1' and 
	oi.ProductId in (16, 26,7,25) and 
	oi.Deleted = 0 and 
	oi.OrderItemTypeId not in (28,29,30)
), 
TargetContacts AS (
	select distinct c.contactid from contacts c 
	inner join OrderCompanies oc on c.CompanyId = oc.ClientId
	inner join ContactActivities ca on c.ContactId = ca.ContactID
	where ca.ActivityId = 11

	union 

	select distinct c.contactid from contacts c 
	inner join companies co  on c.CompanyId = co.CompanyId
	inner join OrderCompanies oc on co.ParentCompanyId = oc.ClientId
	inner join ContactActivities ca on c.ContactId = ca.ContactID
	where ca.ActivityId = 11
)
INSERT INTO ContactActivities (ContactId, ActivityId, SourceId, ActivityDate) 
	SELECT contactid, 200, 174, GETDATE() FROM TargetContacts;

A tiny mental model that tends to stick:

• The first CTE must start with a semicolon:
  ;WITH First AS (...)
• Every subsequent CTE is just:
  , Second AS (...)
, Third AS (...)

Then the final statement (SELECT/INSERT/UPDATE/MERGE) consumes them.