· 7 years ago · Jan 16, 2019, 11:06 PM
1create table if not exists diallogic.bucket
2(
3 clientname varchar(250) not null,
4 extension varchar(250) not null,
5 collect_as varchar(250) not null,
6 email varchar(250) not null,
7 state varchar(200) not null,
8 portfolio varchar(200) not null,
9 phone_id int unsigned not null,
10 maindatabaseid varchar(200) not null,
11 phonenumber varchar(200) not null,
12 assignedto varchar(200) not null,
13 phonetype varchar(200) not null,
14 phonestatus varchar(200) not null,
15 isagoodnumber varchar(50) not null,
16 firstname varchar(200) not null,
17 lastname varchar(200) not null,
18 balance varchar(50) not null,
19 typeofdebt varchar(200) not null,
20 ranknum varchar(50) not null,
21 lastworked varchar(250) null,
22 start_date timestamp default CURRENT_TIMESTAMP not null,
23 dial_date date null,
24 sent char default 'N' null,
25 channel int(3) default 0 not null
26);
27
28
29
30insert into diallogic.bucket (clientname, extension, collect_as, email, state, portfolio, phone_id, maindatabaseid, phonenumber, assignedto, phonetype, phonestatus, isagoodnumber, firstname, lastname, balance, typeofdebt, ranknum, lastworked)
31select clientname, custom53, custom54, custom55, collectionsmax.dbase.state, collectionsmax.dbase.portfolio, collectionsmax.phonenumbers.id, maindatabaseid, replace(phonenumber,'-','') as phonenumber, collectionsmax.phonenumbers.assignedto, phonetype, phonestatus, isagoodnumber, collectionsmax.phonenumbers.firstname, collectionsmax.phonenumbers.lastname, balance, collectionsmax.phonenumbers.typeofdebt
32,(
33 CASE maindatabaseid
34 WHEN @curType
35 THEN @curRow := @curRow + 1
36 ELSE @curRow := 1 AND @curType := maindatabaseid END
37 ),
38 lastworked
39
40 from (SELECT @curRow := 0, @curType := '') r, collectionsmax.phonenumbers left join collectionsmax.dbase on collectionsmax.phonenumbers.maindatabaseid = collectionsmax.dbase.id
41 join (select id, claimstatus from collectionsmax.dbase where claimstatus = 'open') dbs on collectionsmax.phonenumbers.maindatabaseid = dbs.id
42where collectionsmax.dbase.assignedto in ('opendecline','ctf','HOUSE','OPEN ACCT','xxx') and collectionsmax.dbase.statusname in ('newbiz','new dda','MANUAL RETAIL')
43and (right(datechargedoff,2) != '08' or right(datechargedoff,2) != '09' or right(datechargedoff,2) != '10' or right(datechargedoff,2) != '11')
44 ##Below change the client to grab all portfolios that fall within the date range below.
45#and collectionsmax.dbase.clientname in ('SPEEDEECASH')
46and collectionsmax.dbase.custom11 != '0.00'
47and collectionsmax.dbase.originalcreditor != ''
48 ##Need to figure out what date range to use, if we need one. This is a varchar data type.
49and collectionsmax.dbase.lastworked between '10/1/2018' and '12/30/2018'
50and collectionsmax.dbase.originalcreditor != '' and (phonetype in(
51'TLO',
52'UNKNOWN',
53'Home Phone',
54'Cell Phone',
55'HOME',
56'TLO-UNKNOWN',
57'TLO-REL',
58'CELL',
59'Reference',
60'Relative',
61'PRIMARY PHONE',
62'PRIMARY',
63'IDID',
64'LAND',
65'Near By')
66#or (phonetype like 'TLO-REL%')
67)
68and phonestatus in ('UNKNOWN','Good Number','Primary Number','TLO.com','Spanish','Good Skip','AUTO INSERT','Do Not Text','Answering Machine')
69
70order by maindatabaseid, FIELD('maindatabaseid', 'isagoodnumber', 'primary number', 'Debtor', 'HOME' ,'Home Phone', 'CELL', 'Cell Phone', 'TLO ', 'TLO-ASSC', 'TLO-REL');
71
72delete from diallogic.bucket where state in ('ny','az','nd','id','mi','or','in','il') AND collect_as in ('Northwest Solutions');
73
74delete from diallogic.bucket where ranknum >7;
75
76#delete from diallogic.bucket_2 where collect_as = 'Northwest Solutions';
77
78select clientname, count(distinct phonenumber) as counter
79from diallogic.bucket
80group by clientname;