Help: how to extra one-time customers using sql manager

[michldav]

hi guys

trying to figure this out for days...

 

what i want to do:

Make a query to be used with sql manager (p 1.5)

I want to extra all customer info for customers who:

 

made 1 valid purchase (= 1 order with status shipped).

And the order should be within a time frame, say 6-18 months old.

 

 

Anyone has an idea how to construct this query? It would be greatly appreciated.

 

regards

Michael

[vekia]

SELECT * FROM ps_customer c
INNER JOIN ps_orders o on c.id_customer = o.id_customer
INNER JOIN ps_order_history oh ON o.id_order = oh.id_order
WHERE oh.id_order_state = 10 AND (o.date_add BETWEEN '2011-01-30 10:15:55' AND '2012-06-31 14:15:55')

you can change between data params and of course use id_order_State related to shipped state in your shop

[michldav]

hey

thanks for the fast response...

i tried the query, but i get an error:

 ʺid_customerINNERʺ does not exists in the table: ps_orders.

 

Also - where is the one-time-customer action handled?

as i dont want to output customers who place 2 or more valid orders

 

Thanks a lot for your time

[vekia]

 

 

 ʺid_customerINNERʺ does not exists in the table: ps_orders.

check your code, you probably forgot about space betwen id_customer and INNER JOIN

 

 

Also - where is the one-time-customer action handled?

as i dont want to output customers who place 2 or more valid orders 

oops! i forgot about that! i have to rebuild query, sorry

[michldav]

hi -  okay i tried again.. getting the same issue.. with INNER join..

this is pasted directly from my shop:

SELECT * FROM ps_customer c

INNER JOIN ps_orders o on c.id_customer = o.id_customer

INNER JOIN ps_order_history oh ON o.id_order = oh.id_order 

WHERE oh.id_order_state = 4 AND (o.date_add BETWEEN '2011-01-30 10:15:55' AND '2012-06-31 14:15:55')

 

strange..

[Daaaaad]

Hi Michldav, I have the same problem with INNER join... Did you find a way to fix it ?

[michldav]

hi David

I'm sorry - but i had not luck in solving this

[vekia]

query with limitation to 1 order

SELECT *, (SELECT count(*) AS nb from ps_orders po WHERE po.id_customer = o.id_customer GROUP BY po.id_customer) AS number_of_orders FROM ps_customer c
INNER JOIN ps_orders o on c.id_customer = o.id_customer
INNER JOIN ps_order_history oh ON o.id_order = oh.id_order
WHERE oh.id_order_state = "1" AND (o.date_add BETWEEN '2011-01-30 10:15:55' AND '2014-06-31 14:15:55') 
AND (SELECT count(*) AS nb from ps_orders po WHERE po.id_customer = o.id_customer GROUP BY po.id_customer) = 1

change date in where clause (date BETWEEN date)