Join Table one only one record but calculate field based on other rows
inthe join
I am trying to write a query to Identify my subscribers who have abandoned
a shopping cart in the last day but also I need a calculated field that
represents weather or not they have received and incentive in the last 7
days.
I have the following tables
AbandonCart_Subscribers Sendlog
The first part of the query is easy, get abandoners in the last day
select a.* from AbandonCart_Subscribers
where DATEDIFF(day,a.DateAbandoned,GETDATE()) <= 1
Here is my attempt to calculate the incentive but I am fairly certain it
is not correct as IncentiveRecieved is always 0 even when I know it should
not be...
select a.*,
CASE
WHEN DATEDIFF(D,s.SENDDATE,GETDATE()) >= 7
THEN 1
ELSE 0
END As IncentiveRecieved
from AbandonCart_Subscribers a
left join SendLog s on a.EmailAddress = s.EmailAddress and s.CampaignID IS
NULL
where
DATEDIFF(day,a.DateAbandoned,GETDATE()) <= 1
Here is a SQL fiddle with the objects and some data. I would really
appreciate some help.
Thanks
http://sqlfiddle.com/#!3/f481f/1
No comments:
Post a Comment