VICI dialer MYSQL trying to dial every 2, 5 and 7 days [closed]

  Kiến thức lập trình
select date(entry_date), status, phone_number, called_count,date(last_local_call_time), date(email) as converze_call_in_date from vicidial_list
where
date(entry_date) between  date(CONCAT(DATE_ADD(CURDATE(), INTERVAL -30 DAY),' ',CURTIME())) and date(CONCAT(DATE_ADD(CURDATE(), INTERVAL -15 DAY),' ',CURTIME()))
and status not in ('DNCC','xfer','NI','xfern')
and called_count < 6 order by date(entry_date)
asc limit 10;

Where I can change the 15 to 2 but how can I add to have also 5 and 7th day from the day of start dialing even this filter might be wrong, please help

select date(entry_date), status, phone_number, called_count,date(last_local_call_time), date(email) as converze_call_in_date from vicidial_list
where
date(entry_date) between  date(CONCAT(DATE_ADD(CURDATE(), INTERVAL -30 DAY),' ',CURTIME())) and date(CONCAT(DATE_ADD(CURDATE(), INTERVAL -15 DAY),' ',CURTIME()))
and status not in ('DNCC','xfer','NI','xfern')
and called_count < 6 order by date(entry_date)
asc limit 10;

I tried this but even then its not working on VICI dialer

New contributor

Nelson is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

First, let’s see how a date range can be generated:

WITH recursive Date_Ranges AS (
    select '2018-11-30' as Date
   union all
   select Date + interval 1 day
   from Date_Ranges
   where Date < '2018-12-31')
select * from Date_Ranges;

Courtesy to https://dba.stackexchange.com/questions/224182/generate-dates-between-date-ranges-in-mysql

Now, in order to get your dates, you will need to join with this table:

WITH recursive Date_Ranges AS (
    select '2024-01-01' as Date
   union all
   select Date + interval 1 day
   from Date_Ranges
   where Date < '2030-12-31')
select yourtable.*
from yourtable
join Date_Ranges
on mod(date_diff(yourtable.yourfield, Date_Ranges.`Date`)) in (2, 5, 7);

Explanation:

  • we recursively define the date range we desire (you can change this to your liking)
  • we join your table with this range (sorry, you have not given us the schema of your tables, so I took the liberty to abstractise on this and you can replace yourtable and its fields with yours)
  • date_diff computes the difference between two dates and yields the number of days between the two
  • mod computes the mathematical modulo
  • in () checks whether the modulo is correct

LEAVE A COMMENT