Quantity Discount SQL query

Hi Haik - I'm pasting a similar call posted a few months ago....found this using the search feature in the newsgroup for "quantity discount" - hope it helps.

TCWS 10/19/2007 8:23 PM PST Question How do I set discount for the entire department instead of individual items? we give 10% off on any 10+ bottles of Wine, so if I setup the discount on individual items, it gives disoucnt only when customer buys same 10 bottles only... I want to change this so it gives discount on any 10+ wines.. Was this post helpful to you? Reply | Print post TopTop convoluted 10/20/2007 2:51 PM PST Hi TCWS...you can use a mix and match quantity discount scheme and apply it to all the items in your "wine" department - the assumptions here are that you have created a "wine" department and all your wine items have been assigned to that wine department.

First create your wine mix and match discount scheme in SO manager - database - discounts - new- description can be "wine discount", select mix and match percent off - in the bottom pricing schedule enter "10" in quantity and 10 also in Reg.Price - this will create the discount scheme.

I think a serious shortfall in the software is that the inventory wizard does not include the ability to do a mass assignment of a discount scheme - you're forced to go item by item assigning the discount scheme to all the items manually in your dept. - a way around this is with an update query which you can run in SO Administrator....

Connect to your database and open a new query - BACKUP THE DB AND IF POSSIBLE DO THIS AFTER HOURS.

select * from quantitydiscount (this will show you the quantity discount schemes you've created. jot down the ID assigned to your "wine discount" scheme)

select * from department (this will show you your departments; jot down the ID assigned to your wine department)

you're ready for your update query which will assign your wine discount scheme to all items in the wine dept.

update item set quantitydiscountid = x where departmentid = y

in the above query, replace x with the ID assigned to the WINE DISCOUNT SCHEME and replace y with the ID assigned TO YOUR WINE DEPT. This is very important or you will update your data incorrectly and have to recreate your database from your backup - if you need addl help holler - hope this helps.

"TCWS" wrote:

Was this post helpful to you? Reply | Print post TopTop Gaz 10/22/2007 4:44 AM PST Hi TCWS

You could do all of this and much more if you had the promoti> How do I set discount for the entire department instead of individual items?

Was this post helpful to you? Reply | Print post TopTop Afshin 11/17/2007 6:49 PM PST You may want to look in the Retail Real Promotion add-on. Let me know if you want to evaluate it.

I recommend to not do that, according to different item prices...

Let's assume the following:

Item A > Department A1 > Price 10 Item B > Department A1 > Price 15 Item C > Department A1 > Price 20 Quantity discount is buy 2 get 1

If i sell in one transaction the 3 items with 1 quantity the discount will be fire....

This is may not equal your situation but i see that is important to take in consideration....

I prefer to make a quantity discount for every item with a different quantity discount definition.