Oracle SQL Problem – needed to return spesific rows from a table

  Kiến thức lập trình

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.

LEAVE A COMMENT