SQL Magento Sales Report

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.

in app/code/local/Mage/Sales/Model/Mysql4/Report/Shipping.php

After

protected function _aggregateByOrderCreatedAt($from, $to)
{
    try {
        $tableName = $this->getTable('sales/shipping_aggregated_order');
        $writeAdapter = $this->_getWriteAdapter();

        $writeAdapter->beginTransaction();

        if (is_null($from) && is_null($to)) {
            $writeAdapter->query("TRUNCATE TABLE {$tableName}");
        } else {
            $where = (!is_null($from)) ? "so.updated_at >= '{$from}'" : '';
            if (!is_null($to)) {
                $where .= (!empty($where)) ? " AND so.updated_at <= '{$to}'" : "so.updated_at <= '{$to}'";
            }

            $subQuery = $writeAdapter->select();
            $subQuery->from(array('so'=>$this->getTable('sales/order')), array('DISTINCT DATE(so.created_at)'))
                ->where($where);

I add those 4 lines : $subQuery->joinInner :

            $subQuery->joinInner(array('sd'=> $this->getTable('sales/order_datetime')), 
            "`sd`.`entity_id` = `so`.`entity_id`",
            array()
        );


            $deleteCondition = 'DATE(period) IN (' . new Zend_Db_Expr($subQuery) . ')';
            $writeAdapter->delete($tableName, $deleteCondition);
        }


        $columns = array(
            'period'                => "DATE(created_at)",
            'shipping_description'  => 'shipping_description',
            'orders_cou开发者_如何学Gont'          => 'COUNT(entity_id)',
            'total_shipping'        => 'SUM(`base_shipping_amount` * `base_to_global_rate`)',

            'store_id'              => 'store_id',
            'order_status'          => 'status',
            'point_relais'                => 'shipping_description',
            'date_commande'               => "DATE(created_at)",
            'date_livraison'              => "DATE(value)"

        $select = $writeAdapter->select()
            ->from($this->getTable('sales/order'), $columns)
            ->where('state NOT IN (?)', array(
                Mage_Sales_Model_Order::STATE_PENDING_PAYMENT,
                Mage_Sales_Model_Order::STATE_NEW
            ))
            ->where('is_virtual = 0');

            if (!is_null($from) || !is_null($to)) {
                $select->where("DATE(created_at) IN(?)", new Zend_Db_Expr($subQuery));
            }

            $select->group(array(
                "DATE(created_at)",
                'store_id',
                'order_status',
                'shipping_description'
                'point_relais',
                'date_commande',
                'date_livraison',
                'client'
            ));

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.

I think there’s something wrong or missing in

            $subQuery->joinInner(array('sd'=> $this->getTable('sales/order_datetime')), 
            "`sd`.`entity_id` = `so`.`entity_id`",
            array()
        );

I’ve declared the table sales_order_datetime in app/code/Core/Mage/Sales/etc/config.xml with :

                <order_datetime><table>sales_order_datetime</table></order_datetime>

And in the database date_livraison is in the appropriate format

If you could help me. Thanks

you are not returning any columns from the joined table.

 $subQuery->joinInner(array('sd'=> $this->getTable('sales/order_datetime')), 
        "`sd`.`entity_id` = `so`.`entity_id`",
        array('name_of_column')  //   <---This is what is missing.
    );

That is an array of columns to return.