Timeline for Join against two tables to get single combined result set
Current License: CC BY-SA 4.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 29, 2018 at 4:44 | comment | added | Neek | I happen to be trying to integrate Zen Cart with Linnworks, and have found that their GetOrderItems SQL actually needs a DiscountPercent column, not a fixed value (see desktop.linnworks.com/Doc/…). This simplifies things hugely, because, the percentage discount per item is the same, regardless of the actual cost. So we can simply divide the total coupon value by the total order cost and get a percentage, see dbfiddle.uk/… .. this removes the chance of negatives. | |
| Nov 28, 2018 at 6:11 | comment | added | Neek | dbfiddle.uk/… produces an averaged_cost column, which avoids negatives but results in every row in the results having the same value, so all details of original product costs is lost. While this solves the problem of orders with a discount coupon, it would pollute the cost visibility for all non-discounted orders. Boo. | |
| Nov 28, 2018 at 5:50 | comment | added | Neek | That's a nice solution, doesn't involve a UNION because the row representing the coupon is not actually required in the output, only that the prices of the products are reduced according to the coupon's value. I have now run into the problem that if the cost of a product is low, and the coupon value high (e.g. by other products being very expensive a percentage discount coupon's discount value may be quite high) then the amount subtracted results in a negative product cost. dbfiddle.uk/… | |
| Nov 27, 2018 at 19:00 | comment | added | McNets | It is not about ugliness, if it does what is supposed to do. dbfiddle.uk/… | |
| Nov 27, 2018 at 14:19 | vote | accept | Neek | ||
| Nov 27, 2018 at 14:16 | vote | accept | Neek | ||
| Nov 27, 2018 at 14:19 | |||||
| Nov 27, 2018 at 14:11 | history | edited | Neek | CC BY-SA 4.0 |
Added 'Edit' with link to new fiddle.
|
| Nov 27, 2018 at 14:10 | comment | added | Neek | I'd love to make the coupon existence behave just like another product, but 1/ this would involve pretty deep hacking of Zen Cart and it's worth avoiding and 2/ it would still involve a negative value going into the order management system (e.g. price -10 for the coupon line item) which for reasons I must avoid. I made a new fiddle that seems to work, subtracting a fraction of the coupon cost from the product cost, so the totals still add up - see edit to the question. | |
| Nov 27, 2018 at 14:01 | answer | added | McNets | timeline score: 1 | |
| Nov 27, 2018 at 13:57 | comment | added | McNets |
Is there a way to convert Coupon in another product instead of an orders_total?
|
|
| Nov 27, 2018 at 13:22 | history | edited | dbdemon |
added 'mariadb' tag
|
|
| Nov 27, 2018 at 13:20 | comment | added | Neek | What would you do about the troublesome cost issue of wanting the product's price to be decremented by twice the discount amount, bearing in mind that there may be more than one product associated with the order? The problem is that the order management system requires the total cost of the products to exactly equal the orders_totals row with name 'total'. If it's different, it starts to create synthetic items to accommodate the difference. edit actually perhaps having 'nuts' stay at 100, and have the 'coupon' "product" be negative, may work.. I'll create an order like this and see. | |
| Nov 27, 2018 at 13:17 | comment | added | Neek | Doh! Of course I do. My SQL is pretty rusty, I don't seem to need UNION more than once every couple of years. Thank you, if you put that in an Answer I could accept it. | |
| Nov 27, 2018 at 11:25 | review | First posts | |||
| Nov 27, 2018 at 12:57 | |||||
| Nov 27, 2018 at 11:21 | history | asked | Neek | CC BY-SA 4.0 |