CODEWITHSUNDEEP
phpmysqlcodeigniter
SaltedBlowfish
SaltedBlowfish

Reputation: 105

Using CodeIgniter Iterate and Sum Over Weekly Date Range

I have been working on a site where I need to iterate by weeks from a specific starting date to a specific ending date and sum a column for that range, then compare it and return the highest value. I am summing up a column up and trying to return the maximum value to my controller. Here is my model code:

In simpler words, I'm trying to:

Sum all sales for specific week ranges then return the highest week's sales. 

function get_best_week_in_range($rep_id, $start_date, $end_date)
{
    $highest_total = 0;

    $date = $start_date; 

    while($date < $end_date)
    {
        $this->db->select('u.rep_id, s.sales_quantity, sum(s.sales_quantity) as sales_quantity ');
        $this->db->join('sales as s','s.sale_rep_id = u.rep_id');

        $this->db->where('s.date >=', $start_date);
        $this->db->where('s.date <=', $end_date);

        $this->db->group_by('u.rep_id');
        $this->db->order_by('sales_quantity', 'desc');

        $query = $this->db->get('users as u');

        $row = $query->row();

        $highest_total = ($row->sales_quantity > $highest_total) ? $row->sales_quantity : $highest_total;

        $date = strtotime("+1 week", $date);
    } 

    return $highest_total;
}

This code is returning '0' as the highest_total. If anyone can enlighten me, I would appreciate it.

Thanks for any and all help!

Upvotes: 3

Views: 2743

Answers (1)

peterm
peterm

Reputation: 92805

If I understand you correctly you can get all you need with a query like this

SELECT SUM(sales_quantity) weekly_total
  FROM sales 
 WHERE sale_rep_id = $rep_id
   AND date BETWEEN $start_date AND $end_date
 GROUP BY WEEK(date)
 ORDER BY weekly_total DESC
 LIMIT 1

Here is SQLFiddle demo

Since you're getting a value for one sales rep then you don't have to use JOIN just a WHERE condition on sales table.

Now I'm not an expert in codeigniter but your function might look like

function get_best_week_in_range($rep_id, $start_date, $end_date) {
     $this->db->select_sum('sales_quantity', 'weekly_total')
              ->from('sales')
              ->where('sale_rep_id', $rep_id)
              ->where('date >=', $start_date)
              ->where('date <=', $end_date)
              ->group_by('WEEK(date)')
              ->order_by('weekly_total', 'DESC')
              ->limit(1);
    $query = $this->db->get();
    return $query->row('weekly_total');
}

Upvotes: 1

Related Questions