Need help with crazy query

[Zergman]

I'm done with this project, losing my mind and need help.

 

DB table has a column with phone numbers in it along with dates it was submitted.  Was asked to get a percentage of repeat incidents based on the phone numbers within 7 days of the first incident.

 

So i need to

[*]Get all phone numbers for the given month

[*]Check each phone number for repeat entries

[*]Get a tally of how many phone numbers have repeat entries

[*]Calculate a percentage for the month of phone numbers that have repeat entries compared to the total for the month.

 

Been working on this and have no code to show for it cause nothing I write can do this without killing the server or give me what I want.

 

Using codeigniter if that matters.

 

Can anyone get me started?!

[Zergman]

odd, duplicate post

[Zergman]

Still working on this but i'm finding the way i'm querying is just not efficient.

 

Doing as follows.  Sorry for the codeigniter queries

 

//Get phone numbers for the entire month
$this->db->select('stn, submit_time');
$this->db->select('COUNT(stn) AS TOTAL');
$this->db->like('submit_date', YEAR_PREV_MONTH, 'after');
$this->db->not_like('level1', 'Abandon');
$this->db->not_like('stn', '9999999999');
$this->db->not_like('employee_id', '00000000');
$this->db->group_by("stn", "asc");
$data = $this->db->get('data');
$total_for_month = $data->num_rows();

$i = 0;

foreach ($data->result() as $row)
{	
    if($row->TOTAL > 1)
{
	$start = $row->submit_time - 604800;
	$end = $row->submit_time;
	$dateRange = "submit_time BETWEEN '$start' and '$end'";

	$this->db->select('data_id');
	$this->db->where($dateRange, NULL);
	$this->db->where('stn', $row->stn);
	$query = $this->db->get('data');
	$stn_data = $query->row();
	$i++;
}
}

echo "Total Individual STN's Found = ".$total_for_month;
echo "<br>";
echo "Incidents found with duplicate within 7 days = ".$i;
echo "<br>";

$total_distinct = $total_for_month - $i;
echo "Incidents with FCR = ".$total_distinct;
echo "<br>";

$a = $total_distinct * 100;
$b = $a / $total_for_month;

echo "<br><b>Group FCR for current Month = ".round($b, 2)."%</b>";
echo "<br><br>This shows the FCR for all calls for the current month for the entire group.";
echo "<hr />";

die();

 

I know this is the wrong way to do this, but for the life of me, can't figure out any other way.  With it like this, its running like 20,000 queries

[mikosiko]

post your table description

[fenway]

I don't see a raw query.

[Zergman]

post your table description

 

sorry but i'm not sure what you mean by table description

[Zergman]

I don't see a raw query.

 

Raw query as in non-active records?

[fenway]

I don't see a raw query.

 

Raw query as in non-active records?

Sorry for the delayed response -- no, raw query as in the actual SQL statement being sen to the server.

[mikosiko]

post your table description

 

sorry but i'm not sure what you mean by table description

 

table schema.. description... definition... same thing .... the CREATE TABLE sentence