Filter SQL needed!

Any and all non-support discussions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Filter SQL needed!

Postby TanShabani » Fri Apr 22, 2022 11:32 am

Hello folks!

I need to make a filter like: ( ( (status='NI') and (last_local_call_time < CONCAT(DATE_ADD(CURDATE(), INTERVAL -10 DAY),' ',CURTIME()) ) ) or (status != 'NI') )

but a bit more complex, in which to include more statuses and more criteria, like time and called count.
For example: If a lead, today get a NI status, I want to prevent calling it for 3 days, then try after, and if it is still NI call it after 5 days, then try after, and if is still NI call it after 10 days, and if the last attempt is still NI move it to DNC. Want in the same script to make a kind of same rule even for AB / DROP / A / NA (just as an example)
Can someone help me pls?
Posts: 6
Joined: Tue Mar 29, 2022 8:42 am

Re: Filter SQL needed!

Postby williamconley » Wed May 04, 2022 12:32 pm

1) Welcome to the Party! 8-)

2) As you are obviously new here, I have some suggestions to help us all help you:

When you post, please post your entire configuration including (but not limited to) your installation method (7.X.X?) and vicidial version with build (VERSION: 2.X-XXXx ... BUILD: #####-####).

This IS a requirement for posting along with reading the stickies (at the top of each forum) and the manager's manual (available on, both free and paid versions)

You should also post: Asterisk version, telephony hardware (model number is helpful here), cluster information if you have one, and whether any other software is installed in the box. If your installation method is "manual/from scratch" you must post your operating system with version (and the .iso version from which you installed your original operating system) plus a link to the installation instructions you used. If your installation is "Hosted" list the site name of the host.

If this is a "Cloud" or "Virtual" server, please note the technology involved along with the version of that techology (ie: VMware Server Version 2.0.2). If it is not, merely stating the Motherboard model # and CPU would be helpful.

Similar to This:

Vicibox X.X from .iso | Vicidial X.X.X-XXX Build XXXXXX-XXXX | Asterisk X.X.X | Single Server | No Digium/Sangoma Hardware | No Extra Software After Installation | Intel DG35EC | Core2Quad Q6600

3) I see you've altered the example DROP lockout filter. Add "and". Then add that same text again, altered for your next Status. Keep going until they are all in there. Note, of course, that this may overload the CPU while running the hopper script if your system is already heavily loaded, as micromanaging the hopper is not without cost (CPU cost, that is). Vicidial is primarily designed to call ALL the leads. Reset the lists. And call ALL the leads again.

4) You may consider writing a custom "list reset" script that will leave the undesirable leads marked "dialed" to keep them out of the hopper until they mature.

5) In all cases: Beware time zones for last local call time. The "curtime" is based on server time. The "last_local_call_time" is based on the local time zone of the lead. Thus comparing those two could bring about undesired calls or omissions if your server and the lead are in different time zones.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta # 352-269-0000 # +44(203) 769-2294
Posts: 19967
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Return to General Discussion

Who is online

Users browsing this forum: No registered users and 30 guests