Get sales of products by week wise as week1,week2, week3, week4

Question

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.

Answer 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.