title: SQL Magento Sales Report
body: <p>I’m trying to add new columns in report/sales/shipping. Columns are now ok but I can’t display values coming from another table.</p>
  
  <p>in app/code/local/Mage/Sales/Model/Mysql4/Report/Shipping.php</p>
  
  <p>After</p>
  
  <pre><code>protected function _aggregateByOrderCreatedAt($from, $to)
  {
      try {
          $tableName = $this-&gt;getTable('sales/shipping_aggregated_order');
          $writeAdapter = $this-&gt;_getWriteAdapter();
  
          $writeAdapter-&gt;beginTransaction();
  
          if (is_null($from) &amp;&amp; is_null($to)) {
              $writeAdapter-&gt;query("TRUNCATE TABLE {$tableName}");
          } else {
              $where = (!is_null($from)) ? "so.updated_at &gt;= '{$from}'" : '';
              if (!is_null($to)) {
                  $where .= (!empty($where)) ? " AND so.updated_at &lt;= '{$to}'" : "so.updated_at &lt;= '{$to}'";
              }
  
              $subQuery = $writeAdapter-&gt;select();
              $subQuery-&gt;from(array('so'=&gt;$this-&gt;getTable('sales/order')), array('DISTINCT DATE(so.created_at)'))
                  -&gt;where($where);
  </code></pre>
  
  <p>I add those 4 lines : $subQuery->joinInner :</p>
  
  <pre><code>            $subQuery-&gt;joinInner(array('sd'=&gt; $this-&gt;getTable('sales/order_datetime')), 
              "`sd`.`entity_id` = `so`.`entity_id`",
              array()
          );
  
  
              $deleteCondition = 'DATE(period) IN (' . new Zend_Db_Expr($subQuery) . ')';
              $writeAdapter-&gt;delete($tableName, $deleteCondition);
          }
  
  
          $columns = array(
              'period'                =&gt; "DATE(created_at)",
              'shipping_description'  =&gt; 'shipping_description',
              'orders_count'          =&gt; 'COUNT(entity_id)',
              'total_shipping'        =&gt; 'SUM(`base_shipping_amount` * `base_to_global_rate`)',
  
              'store_id'              =&gt; 'store_id',
              'order_status'          =&gt; 'status',
              'point_relais'                =&gt; 'shipping_description',
              'date_commande'               =&gt; "DATE(created_at)",
              'date_livraison'              =&gt; "DATE(value)"
  
          $select = $writeAdapter-&gt;select()
              -&gt;from($this-&gt;getTable('sales/order'), $columns)
              -&gt;where('state NOT IN (?)', array(
                  Mage_Sales_Model_Order::STATE_PENDING_PAYMENT,
                  Mage_Sales_Model_Order::STATE_NEW
              ))
              -&gt;where('is_virtual = 0');
  
              if (!is_null($from) || !is_null($to)) {
                  $select-&gt;where("DATE(created_at) IN(?)", new Zend_Db_Expr($subQuery));
              }
  
              $select-&gt;group(array(
                  "DATE(created_at)",
                  'store_id',
                  'order_status',
                  'shipping_description'
                  'point_relais',
                  'date_commande',
                  'date_livraison',
                  'client'
              ));
  </code></pre>
  
  <p>At the end “value” in 'date_livraison' is the only variable coming from table sales/order_datetime and I can’t display it.
  Others variables are coming from table sales/order and are well displayed.</p>
  
  <p>I think there’s something wrong or missing in </p>
  
  <pre><code>            $subQuery-&gt;joinInner(array('sd'=&gt; $this-&gt;getTable('sales/order_datetime')), 
              "`sd`.`entity_id` = `so`.`entity_id`",
              array()
          );
  </code></pre>
  
  <p>I’ve declared the table sales_order_datetime in app/code/Core/Mage/Sales/etc/config.xml with :</p>
  
  <pre><code>                &lt;order_datetime&gt;&lt;table&gt;sales_order_datetime&lt;/table&gt;&lt;/order_datetime&gt;
  </code></pre>
  
  <p>And in the database date_livraison is in the appropriate format</p>
  
  <p>If you could help me.
  Thanks</p>