tmFrVRHp

1drop table if exists phonenumbers_t;
2
3create table  phonenumbers_t select 'r1c1' as dialphase,  custom53 as security_phrase, custom54 as email, custom55, dbase.state, dbase.portfolio, phonenumbers.id, maindatabaseid, replace(phonenumber,'-','') as phonenumber, phonenumbers.assignedto, phonetype, phonestatus, isagoodnumber, phonenumbers.firstname, phonenumbers.lastname, balance, phonenumbers.typeofdebt
4,(
5           CASE maindatabaseid
6           WHEN @curType
7           THEN @curRow := @curRow + 1
8           ELSE @curRow := 1 AND @curType := maindatabaseid END
9         )  AS rank
10       
11         from (SELECT @curRow := 0, @curType := '') r, phonenumbers left join dbase on phonenumbers.maindatabaseid = dbase.id
12         join (select id, claimstatus from dbase where claimstatus = 'open') dbs on phonenumbers.maindatabaseid = dbs.id
13where   dbase.assignedto  in ('opendecline','ctf','HOUSE','OPEN ACCT','xxx') and dbase.statusname  in ('newbiz','new dda','MANUAL RETAIL')
14and (right(datechargedoff,2) != '08' or right(datechargedoff,2) != '09' or right(datechargedoff,2) != '10' or right(datechargedoff,2) != '11') or right(datechargedoff,2) != '12'
15#and dbase.portfolio   in ('20180514 CNG202 PDL') and dbase.custom11 != '0.00'
16and dbase.portfolio in ('LEFRONTIER3v2_12007')and dbase.custom11 != '0.00'
17#and state not in ('ny','az','nd','id','mi','or','in','il') 
18and originalcreditor != ''
19and dbase.originalcreditor != '' and (phonetype in(
20'TLO',
21'UNKNOWN',
22'Home Phone',
23'Cell Phone',
24'HOME',
25'TLO-UNKNOWN',
26'TLO-REL',
27'CELL',
28'Reference',
29'Relative',
30'PRIMARY PHONE',
31'PRIMARY',
32'IDID',
33'LAND',
34'Near By') 
35#or (phonetype like 'TLO-REL%')
36)
37and phonestatus in ('UNKNOWN','Good Number','Primary Number','TLO.com','Spanish','Good Skip','AUTO INSERT','Do Not Text','Answering Machine') 
38
39order by maindatabaseid, FIELD('maindatabaseid', 'isagoodnumber', 'primary number', 'Debtor', 'HOME' ,'Home Phone', 'CELL', 'Cell Phone', 'TLO ', 'TLO-ASSC', 'TLO-REL');
40
41delete from phonenumbers_t where state in ('ny','az','nd','id','mi','or','in','il') AND email  in ('Northwest Solutions');
42
43delete from phonenumbers_t where rank >7;
44
45delete from phonenumbers_t where left(phonenumber,3) in ('899','888','877','866','855','844','833','822','811','800','555');
46
47select * from phonenumbers_t;
48
49#select * from dbase where portfolio = '2A0318D';