SQL Assistance

[GMM148]

Anyone out there who is good at SQL who could put together a report format for me based on particular requirements? Happy to pay reasonable amount to be agreed beforehand.

Site is non-commercial, it's a club website, just need to run reports on who has booked for when and how many?

THanks

 

[joseantgv]

What do you need?

[GMM148]

Hi thanks and sorry for the delay.

I already have an SQL query but need to customise it for better output for particular product orders, given that this is for a club, so people book in and then cancel/change bookings. The current query is shown below:

SELECT DISTINCT c.`id_customer`, CONCAT(c.`firstname`,' ', c.`lastname`) as customer, c.`email` 
FROM `ps_customer` c LEFT JOIN `ps_orders` o ON (c.`id_customer` = o.`id_customer`) LEFT JOIN `ps_order_detail` od ON o.`id_order` = od.`id_order`
WHERE od.`product_id` = 13

I want to keep this report but ensure the following:

1. Product ID remains at the end so it be quickly edited

2. I only want to show Customers whose orders have been 'DELIVERED' - if they have subsequently CANCELLED I don't want them to show

3. Currently if an 'order/booking' is 'returned to stock' it still shows up on the output list - I don't want to show 'returned to stock' orders

4. I want to show how many items a customer has ordered - currently even if they have ordered multiples, even if they have done it on separate occasions, it shows up just once - so I need to see 'product quantity'

Any thoughts appreciated.

thanks