this is always great to have your help.
Im working with campaign managment system called Unica that perfoms sql querys behind the scenes.
In my job i have a contacthistory table which holds the contacts for each customer who turns out to be suitable for the population in the campaign.
We have 4 contactstatusid (1-find suitable, 2-send to a channel, 5-contacted, 6-lead closed)
The logic goes like that status number 1 comes first and a few minutes later after the communication channel get this customer the status will change to 2. and 5 comes while some status in the CRM was change.
We can get more than one rows with the same customerid but any customer can be suitable for the campaign once a day but more than one time in a life time.
I want to get customer ids for customers that have any row with status id number 2 and they don’t have fitted row with status id 5. (they are fitted if the contactdatetime for both status are in the same day) and i need the rows for channel = ‘CRM’
I tried to tell if this customer have status id 5 so don’t give him back but than i miss alot of rows of this customer that don’t have fitted status id 5.
I’ve also tried to make some joins and things but i didn’t get the right outcomes.
select * from unica_campaign.ua_contacthistory_dlv
where channel = 'CRM'
and a.contactstatusid = '2'
and contactdate_time not in
(select a.contactdatetime
from unica_campaign.ua_contacthistory_dlv a
full outer join unica_campaign.ua_contacthistory_dlv b
on a.custid = b.custid
where a.channel = b.channel
and extract(day from a.contactdatetime)=extract(day from b.contactdatetime)
and extract(month from a.contactdatetime)=extract(month from b.contactdatetime)
and extract(year from a.contactdatetime)=extract(year from b.contactdatetime)
and a.channel = 'CRM'
and a.contactstatusid = '2'
and b.contactstatusid = '5'
);
Please help me !
Thank you very much.