Tuesday, 27 August 2013

Join Table one only one record but calculate field based on other rows inthe join

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