This is the original sql query:
- Code: Select all
SELECT recording_log.lead_id,recording_id,start_time,filename,location,status FROM recording_log,vicidial_log where start_time > '$BEGINdate' and start_time < '$ENDdate' and location IS NOT NULL and location NOT IN('','NOT_FOUND','NOT_FOUND_2','DELETED') and vicidial_log.status IN($save_statusesSQL) and call_date > '$BEGINdate' and call_date < '$ENDdate' and recording_log.lead_id=vicidial_log.lead_id and recording_log.user=vicidial_log.user order by recording_id LIMIT 500000;
First I've tried to run it without modifications, but maybe my configuration is not correct, because "location" is always NULL in my table, so the query selects 0 rows.
BTW, I've cutted away (only for this test) the location filter, and this is the modified query (I'm testing the query on phpmyadmin, so this is pure sql, without variables):
- Code: Select all
SELECT recording_log.lead_id, recording_id, start_time, filename, location, vicidial_log.status
FROM recording_log, vicidial_log, vicidial_statuses, vicidial_campaign_statuses
WHERE start_time > '2007-12-01 00:00:00'
AND start_time < '2007-12-31 23:59:59'
AND call_date > '2007-12-01 00:00:00'
AND call_date < '2007-12-31 23:59:59'
AND recording_log.lead_id = vicidial_log.lead_id
AND recording_log.user = vicidial_log.user
AND ((vicidial_log.status = vicidial_statuses.status) OR (vicidial_log.status = vicidial_campaign_statuses.status))
AND ((vicidial_statuses.category IN ('NEGATIVI', 'OK')) AND (vicidial_campaign_statuses.category IN ('NEGATIVI', 'OK')))
ORDER BY recording_id
LIMIT 500000
Well, this is the result:
- Code: Select all
2190 90 2007-12-11 16:03:17 20071211-160316_087491233 NULL KONI
2190 90 2007-12-11 16:03:17 20071211-160316_087491233 NULL KONI
2190 90 2007-12-11 16:03:17 20071211-160316_087491233 NULL KONI
2190 90 2007-12-11 16:03:17 20071211-160316_087491233 NULL KONI
2190 90 2007-12-11 16:03:17 20071211-160316_087491233 NULL KONI
2190 90 2007-12-11 16:03:17 20071211-160316_087491233 NULL KONI
2190 90 2007-12-11 16:03:17 20071211-160316_087491233 NULL KONI
2190 90 2007-12-11 16:03:17 20071211-160316_087491233 NULL KONI
2190 90 2007-12-11 16:03:17 20071211-160316_087491233 NULL KONI
2190 90 2007-12-11 16:03:17 20071211-160316_087491233 NULL KONI
2082 92 2007-12-11 16:08:55 20071211-160854_087466538 NULL KONI
2082 92 2007-12-11 16:08:55 20071211-160854_087466538 NULL KONI
2082 92 2007-12-11 16:08:55 20071211-160854_087466538 NULL KONI
2082 92 2007-12-11 16:08:55 20071211-160854_087466538 NULL KONI
2082 92 2007-12-11 16:08:55 20071211-160854_087466538 NULL KONI
2082 92 2007-12-11 16:08:55 20071211-160854_087466538 NULL KONI
2082 92 2007-12-11 16:08:55 20071211-160854_087466538 NULL KONI
2082 92 2007-12-11 16:08:55 20071211-160854_087466538 NULL KONI
2082 92 2007-12-11 16:08:55 20071211-160854_087466538 NULL KONI
2082 92 2007-12-11 16:08:55 20071211-160854_087466538 NULL KONI
2208 95 2007-12-11 16:16:11 20071211-161610_087492003 NULL KOTA
2208 95 2007-12-11 16:16:11 20071211-161610_087492003 NULL KOTA
2208 95 2007-12-11 16:16:11 20071211-161610_087492003 NULL KOTA
2208 95 2007-12-11 16:16:11 20071211-161610_087492003 NULL KOTA
2208 95 2007-12-11 16:16:11 20071211-161610_087492003 NULL KOTA
2208 95 2007-12-11 16:16:11 20071211-161610_087492003 NULL KOTA
2208 95 2007-12-11 16:16:11 20071211-161610_087492003 NULL KOTA
2208 95 2007-12-11 16:16:11 20071211-161610_087492003 NULL KOTA
2208 95 2007-12-11 16:16:11 20071211-161610_087492003 NULL KOTA
2208 95 2007-12-11 16:16:11 20071211-161610_087492003 NULL KOTA
(KONI and KOTA are campaign based custom statuses)
As you can see, it seems to select the correct rows, but they are "replicated"...
Does anyone can help me?
Thank you very much in advance.
PS: Maybe, for sales report, is a good idea to select dispos by categories, too
