How to export product list with prices & modified (discount) prices

[core-]

Hi

 

Could someone give an sql querry to get list of all products with their base prices & price reductions (discount prices) /if there is any reduction/.

Even better if the querry could include category selection.

 

So i need a product list with base prices and modified prices next to each other.

 

 

Many thanks in advance

[phrasespot]

A product may have more than one price reduction (e.g. $5 off if bought 3, or 10% off in September)

 

Use the following (modify table prefix if required, does not take tax into account in base price, reductions may be with or w/o tax):

 

SELECT DISTINCT
p.id_product AS 'Product ID',
pl.name AS 'Product name',
cl.name AS 'Default category',
p.price AS 'Base price',
sp.reduction_type AS 'Reduction type',
sp.reduction AS 'Reduction',
sp.from_quantity AS 'For quantity',
sp.from AS 'Start date',
sp.to AS 'End date'
FROM
ps_product p
	LEFT JOIN
(ps_product_lang pl
CROSS JOIN ps_category_lang cl
CROSS JOIN ps_specific_price sp) ON (p.id_product = pl.id_product AND p.id_category_default = cl.id_category AND p.id_product = sp.id_product)
WHERE
sp.reduction_type IS NOT NULL
ORDER BY p.id_product;

 

This does not give you base and modified prices side-by-side as it would require quite a bit of domain knowledge (taxes, currencies, languages, groups etc available in the shop) to be able to calculate reduced prices or use a mile long SQL query to extract and assemble that info. Export the result to spreadsheet and do the calculation there.

[core-]

A product may have more than one price reduction (e.g. $5 off if bought 3, or 10% off in September)

 

Use the following (modify table prefix if required, does not take tax into account in base price, reductions may be with or w/o tax):

 

SELECT DISTINCT
p.id_product AS 'Product ID',
pl.name AS 'Product name',
cl.name AS 'Default category',
p.price AS 'Base price',
sp.reduction_type AS 'Reduction type',
sp.reduction AS 'Reduction',
sp.from_quantity AS 'For quantity',
sp.from AS 'Start date',
sp.to AS 'End date'
FROM
ps_product p
	LEFT JOIN
(ps_product_lang pl
CROSS JOIN ps_category_lang cl
CROSS JOIN ps_specific_price sp) ON (p.id_product = pl.id_product AND p.id_category_default = cl.id_category AND p.id_product = sp.id_product)
WHERE
sp.reduction_type IS NOT NULL
ORDER BY p.id_product;

 

This does not give you base and modified prices side-by-side as it would require quite a bit of domain knowledge (taxes, currencies, languages, groups etc available in the shop) to be able to calculate reduced prices or use a mile long SQL query to extract and assemble that info. Export the result to spreadsheet and do the calculation there.

 

Thanks phrasespot and sorry for the delay. I'll try the query.

[chetwyn]

what about to include combination discounts aswell ? need some help

[Juanaco]

On 8/23/2012 at 4:09 PM, core- said:

 

Thanks phrasespot and sorry for the delay. I'll try the query.

Hello, the query works fine, but it duplicates all the products, anybody knows how to fix it?