I have this query to get the prices of all sold products of a shipping date:

SELECT ShippingDay,
       round(Sale_in_Euro,2) AS Sales,
SUM(SELECT (cped.value * sfoi.qty_ordered)
                 FROM catalog_product_entity_decimal AS cped
                 WHERE cped.entity_id = sfoi.product_id
                   AND cped.attribute_id = 99)) AS Sale_in_Euro
FROM sales_flat_order AS sfo
   INNER JOIN sales_flat_order_item AS sfoi ON sfoi.order_id=sfo.entity_id
   WHERE (sfo.status = 'processing'
          OR sfo.status = 'pending')
   GROUP BY sfo.shipping_arrival_date
   ORDER BY sfo.shipping_arrival_date DESC) AS resulttable

However cped.value is the value of the product including taxes. I now want to include that the taxes are substracted from the calculation, but I am a bit confused where to find the appropriate information.

I find the tax_class_id of the product in catalog_product_index_price - then there are other tables like tax_calculation_rate and tax_calculation, but how are they connected? Any suggestion how I can substract the taxes from the product prices in the query above?

And what is stored in sales_order_tax_item?

Your Answer

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

Browse other questions tagged or ask your own question.