Hello I am a bit of an SQL and Presta newbie so would love some help on this which will probably be useful to others.
I have set up an SQL (part my work part cobbled together from other queries I can find) to get a weekly order report.
I can see customers, addresses, value and any discounts from this query but I cannot work out how to join the cart_rule to get the Voucher Code used.
This is my current query without the Voucher code field which works great.
SELECT o.`id_order` AS `id`,
CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `Customer`,
CONCAT(LEFT(ad.`firstname`, 1), '. ', ad.`lastname`) AS `Del_Name`,
c.`email` AS `email`,
ad.`address1` AS `Address1`,
ad.`address2` AS `Address2`,
ad.`city` AS `City`,
ad.`postcode` AS `Postcode`,
o.`payment`, CONCAT(o.`total_paid_real`, ' ', cu.`sign`) AS `Total`,
o.`date_add` AS `Date`,
o.`total_discounts` AS `Discount`
FROM `ps_orders` o
LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)
where o.date_add between '2015-03-25' and '2015-04-01'
However when I try and add the Voucher Code from the ps_cart_rule table it only returns a blank even if a Voucher has been used.
This is my latest attempt...
SELECT o.`id_order` AS `id`,
CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `Customer`,
CONCAT(LEFT(ad.`firstname`, 1), '. ', ad.`lastname`) AS `Del_Name`,
c.`email` AS `email`,
ad.`address1` AS `Address1`,
ad.`address2` AS `Address2`,
ad.`city` AS `City`,
ad.`postcode` AS `Postcode`,
o.`payment`, CONCAT(o.`total_paid_real`, ' ', cu.`sign`) AS `Total`,
o.`date_add` AS `Date`,
o.`total_discounts` AS `Discount`,
cr. `code` AS 'CartCode'
FROM `ps_orders` o
LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)
LEFT JOIN `ps_cart_rule` cr ON (o.`id_customer` = cr.`id_customer`)
where o.date_add between '2015-03-25' and '2015-04-01
I am probably missing something obvious but what appreciated a bit of guidance on what I am doing wrong.
Thanks
Mike