2

I have a requirement to make a sales report like this:

Product Name         First Week   Second Week  Third Week
   a                      12            6           2
   b                       0            0           4 
   c                       0            3           0  

For this I made a function which returns sku and sum(qty_ordered) like

   $query = Mage::getResourceModel('sales/order_item_collection');
   $query->getSelect()->reset(Zend_Db_Select::COLUMNS)
    ->columns(array('sku','qty_ordered'))
    ->where(new Zend_Db_Expr('created_at BETWEEN NOW()-INTERVAL 1 WEEK AND NOW()'))
    ->group(array('sku'));

sql query for the above is:

SELECTmain_table.sku,main_table.qty_ordered, SUM(main_table.qty_ordered) ASweek1FROMsales_flat_order_itemASmain_tableWHERE (created_at BETWEEN NOW()-INTERVAL 1 WEEK AND NOW()) GROUP BYsku;

But the above returns for only 1 week. If I change the created_at BETWEEN NOW()-INTERVAL 1 WEEK AND NOW() to created_at BETWEEN NOW()-INTERVAL 2 WEEK AND NOW() then it returns 2 weeks sales.. But I want the result as shown in the above format. So how can I customize this. Help me on this.

3
  • You should be able to group by WEEK(created_at) I believe, and then just make sure that your WHERE includes the range you're looking for (i.e. 3 weeks to now) Mar 12, 2014 at 15:22
  • Douglas, you should post this as an answer! Mar 13, 2014 at 5:08
  • But how can I get data of another two columns ie., week2 and week3? Mar 13, 2014 at 5:29

1 Answer 1

1

As suggested in my comment, you should be able to use the WEEK(created_at) query to find this. So (untested) but along the lines of:

$numberOfWeeks = 3;

$query = Mage::getResourceModel('sales/order_item_collection');
$query->getSelect()->reset(Zend_Db_Select::COLUMNS)
->columns(array('created_at','sku','qty_ordered'))
->where(new Zend_Db_Expr('created_at BETWEEN NOW()-INTERVAL '.$numberOfWeeks.' WEEK AND NOW()'))
->group(new Zend_Db_Expr('WEEK(created_at)'));

This should then return something like the following:

2014-03-15   100
2014-03-08   90
2014-03-01   110

The first column is the week starting date, the second is the amount sold in that week.

You can iterate through this to produce a table view of the data where required.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.