Cristina Alonso Posted February 25, 2019 Share Posted February 25, 2019 Buenos días, Necesito ayuda. Estoy intentado sacar una sentencia sql que me saque todos los pedidos y luego otra columna que me diga si tiene un % de cupón de descuento o no. He conseguido que me muestre los pedidos que si tienen cupón, pero los que no tienen cupón (0%) se quedan fuera y no soy capaz de sacarlo. Cabe decir, que hago distinción entre descuentos y cupón de descuento. Mi sentencia sql es la siguiente me gustaría que echaséis una mano porque no veo por dónde tirar. Mil gracias Select product_reference as ID, d.invoice_number as Factura, e.product_name as Nombre, d.id_order as Pedido, h.date_add as Fecha, concat(firstname,lastname) as Cliente, e.product_quantity as Cantidad , ROUND((total_paid),2) as Total , ROUND((total_paid-total_shipping),2) as TotalProductos , concat(ROUND((e.reduction_percent),0), ' %') as Descuento, CASE WHEN o.id_order=d.id_order and o.id_cart_rule=r.id_cart_rule THEN concat(ROUND((r.reduction_percent),0), ' %') <- Aquí saco los pedidos con cupón pero jamas se va por el else ELSE '%0' END as Cupon3, CASE WHEN (h.id_order_state=1 or h.id_order_state=10) THEN '-' WHEN ROUND((d.total_discounts*100)/d.total_products_wt,0) != 0 THEN concat("-", ROUND(ROUND( ((e.unit_price_tax_incl*e.product_quantity_refunded)),2)-ROUND( ((e.unit_price_tax_incl*e.product_quantity_refunded)),2)*ROUND((d.total_discounts*100)/d.total_products_wt,0)/100,2)) WHEN ROUND((d.total_discounts*100)/d.total_products_wt,0) = 0 and d.current_state=25 or d.current_state=26 then concat("-",ROUND( ((e.unit_price_tax_incl*e.product_quantity_refunded)),2)) WHEN ROUND((d.total_discounts*100)/d.total_products_wt,0) = 0 and d.current_state=27 or d.current_state=28 then concat("-",ROUND( ((e.unit_price_tax_incl*e.product_quantity_refunded)),2)) ELSE '-0.00' END as Devolucion, CASE WHEN h.id_order_state=28 THEN 'Devolucion Parcial' WHEN h.id_order_state=26 THEN 'Devolucion' END as Estado, CASE WHEN id_carrier=13 THEN 'Tienda' WHEN id_carrier=15 THEN 'Domicilio' ELSE 'INDEFINIDO' END as Entrega , g.quantity as Stock, d.date_add as FPedido, 'Tarjeta' as Pago from ps_group_lang a, ps_customer_group b, ps_customer c, ps_orders d, ps_order_detail e , ps_stock_available g, ps_order_history h, ps_tax_rule i, ps_tax j, ps_country k, ps_order_cart_rule o, ps_cart_rule r <- ÉSTAS DOS ÚLTIMAS TABLAS SON LAS QUE AÑADO PARA SACAR LOS CUPONES DE DESCUENTO where o.id_order=d.id_order or o.id_order!=d.id_order and o.id_cart_rule=r.id_cart_rule <- Y AQUÍ SALGO TAMBIÉN PARA QUE ME SAQUE LOS PEDIDOS CON CUPÓN and a.id_group=b.id_group and b.id_customer=c.id_customer and a.id_lang=c.id_lang and c.id_customer=d.id_customer and d.id_order=e.id_order and g.id_product=e.product_id and d.id_order=h.id_order and (h.id_order_state=10 or h.id_order_state=26 or h.id_order_state=28) and (c.id_default_group=4 or c.id_default_group=5) and e.id_tax_rules_group=i.id_tax_rules_group and i.id_tax=j.id_tax and i.id_country=k.id_country and k.id_country=6 and i.id_state=0 order by Fecha Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now