Additional Targeting within Pre-Built SQL

Transact-SQL
1

I have a pre-built SQL which is used to automatically target customers within a set transactional range with a certain marketing message (let's call this A), it looks at a customer's transactions in X time-period.

The current pre-built SQL excludes variables such as specific countries, different types of accounts etc.

I am looking to use this SQL to show an additional marketing message (let's call this B) to customers in certain countries.

So customer 1 who is within X% of <$ TARGET> and is NOT in country X sees marketing messages A & B. However customer 2, who is within the same % of and DOES live in country Y only sees marketing message A.

Does anyone know how to go about adding this in to the current pre-built SQL?

2

Is this real world situation? Or is this job interview or homework?

3

real world

4

i think views will help

or you can write SQL
with case when

just initial thoughts

Please provide your thoughts

5

Can you share your data structure as it is now?
we need sample tables and sample data like the following

create table #customers(customerid int not null , 
customername varchar(50),countryofResidence varchar(30) )
create table #customerTransactions(customerid int not null, 
transactiondate datetime, transactionAmount money)
create table #marketingMessage(messageid int not null, 
marketingMessage varchar(250), isExpired bit default((0)) )
create table #targets(targetAmount decimal(4,1), targetYear int)

insert into #targets
select 1.5, 2018 union
select 50, 2019 

insert into #customers
select 1, 'Customer 1', 'Tatooine' union
select 2, 'Customer 2' , 'Naboo'

insert into #customerTransactions
select distinct 1, DATEADD(mm,column_id, getdate()), column_id * 0.99
  from sys.columns
  where column_id between 1 and 9
union
select distinct 2, DATEADD(mm,column_id, getdate()), column_id * 0.99
  from sys.columns
  where column_id between 1 and 7

insert into #marketingMessage
select 1, '50% off with purchase of hoverboard!', 1 union
select 2, '20% off with purchase of toothbrush!', 1 union
select 3, 'Buy 1 get 3 free kittens!', 1

;with cte
as
(
select c.customername, 
       sum(ct.transactionAmount) Amount, 
	   t.targetYear
  From #customers c
   join #customerTransactions ct on c.customerid = ct.customerid
   join #targets t on t.targetYear = year(ct.transactiondate)   
group by c.customername, t.targetYear
)
select * , 
       case 
	      when ( (100 - (Amount/t.targetAmount) * 100) between 12 and 13 )  then 'HA'
		  else 'HO'
	   end as marketingMessage
  From cte 
  join #targets t on cte.targetYear = t.targetYear


drop table #customerTransactions
drop table #customers
drop table #marketingMessage
drop table #targets