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