Emails - Customer spend > 500 in 1 year
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = (select dateadd(year,-1,getdate()))
SET @endDate = getdate()
select
mailinglist.mailinglistid,
mailinglist.emailaddress
from Customers c
inner join orders o on o.customerid = c.customerid
inner join mailinglist on mailinglist.customerid = c.customerid
where o.timestamp between @startDate and @endDate and mailinglist.mailinglistid > 0
group by
mailinglist.mailinglistid,
mailinglist.emailaddress
having sum (o.totalprice)>=500
Email - addresses of subscribed customers who have ordered in last 3 years
SELECT
Customers.EmailAddress
FROM
Customers
INNER JOIN MailingList
ON MailingList.CustomerID = Customers.CustomerID
INNER JOIN
Orders
ON Orders.CustomerID = Customers.CustomerID
WHERE
MailingList.MailingListID IN (
SELECT
MailingListID
FROM
MailingListEmailProfiles
INNER JOIN EmailProfiles
ON EmailProfiles.EmailProfileID = MailingListEmailProfiles.EmailProfileID
)
AND
(
Orders.Timestamp BETWEEN DateAdd(yy, -3, GetDate()) AND GETDATE()
)