Quantity Discount SQL query

Point-Of-Sale Software - - MS Point Of Sale software discussed here 

Yahoo!   Google   Windows Live   del.icio.us   digg   Netscape

Subject Author Date Quantity Discount SQL query Haik 01-06-2008  RE: Quantity Discount SQL query convoluted 01-07-2008  RE: Quantity Discount SQL query Haik 01-07-2008  Re: Quantity Discount SQL query Mahmoud Amin 01-08-2008
Posted by Haik on January 6, 2008, 7:09 pm Please log in for more thread options Hi, I am looking for a SQL query that will let me change the quantity discounts based on a specific department or category. (Example: assign a buy 2 get 1 half off discount to all the items in the men’s department) Any kind of help is appreciated. Thank you in advance, Posted by convoluted on January 7, 2008, 3:57 pm Please log in for more thread options 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: > 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                                    Gaz         10/22/2007 4:44 AM PST                           Hi TCWS You could do all of this and much more if you had the promotions module add-on from RMSynergy. Check with your partner if they can get it for you or not, it really is amazing. "TCWS" wrote: > 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                                    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. Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = == = = = = = = = = > 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.. "Haik" wrote: > Hi, I am looking for a SQL query that will let me change the quantity > discounts based on a specific department or category. (Example: assign a buy > 2 get 1 half off discount to all the items in the men’s department) Any kind > of help is appreciated. > > Thank you in advance, > > > Posted by Haik on January 7, 2008, 4:35 pm Please log in for more thread options Thank you, I got what i was looking for. "convoluted" wrote: > 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: > > > 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          >          > > > >          >         Gaz         10/22/2007 4:44 AM PST          >          >         Hi TCWS > > You could do all of this and much more if you had the promotions module > add-on from RMSynergy. > Check with your partner if they can get it for you or not, it really is > amazing. > > "TCWS" wrote: > > > 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          >          > > > >          >         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. > > Afshin Alikhani - [ afshin@retailrealm.co.uk ] > CEO - Retail Realm > = == = = = = = = = = > > 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.. > > "Haik" wrote: > > > Hi, I am looking for a SQL query that will let me change the quantity > > discounts based on a specific department or category. (Example: assign a buy > > 2 get 1 half off discount to all the items in the men’s department) Any kind > > of help is appreciated. > > > > Thank you in advance, > > > > > > Posted by Mahmoud Amin on January 8, 2008, 5:17 am Please log in for more thread options 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. > Hi, I am looking for a SQL query that will let me change the quantity > discounts based on a specific department or category. (Example: assign a > buy > 2 get 1 half off discount to all the items in the men’s department) Any > kind > of help is appreciated. > > Thank you in advance, > > > Similar Threads Posted Sql Query for Single & parent item Quantity April 21, 2009, 1:47 am quantity discount June 13, 2007, 8:30 pm Quantity Discount December 12, 2008, 5:00 pm quantity discount on returns June 3, 2006, 8:07 pm quantity discount on returns September 28, 2006, 3:31 pm Layaways - Quantity Discount Bug? October 28, 2008, 7:12 pm layaways - quantity discount bug November 20, 2008, 12:42 pm item quantity discount not found December 20, 2008, 11:23 am Quantity Discount Pricing Table January 20, 2009, 1:58 pm Allow for global price changing in the quantity discount table August 9, 2005, 10:11 am