I would like to make all items that are on sale "not-discountable" without having to open each item to mark the "not-discountable" box. Does someone have a query for that? Also, then after the sale, I'd like to run a query to turn off the "not-discountable" option. The only catch here is that I have some items that are always "not-discountable", so I'd want to make sure they don't get messed with. Any ideas?
You could set a flag in one of the unused fields to identify your 'always non-discountable' items. For example, set Price B to $99.99 and when you run your query to change the non-discountable flag from 1 to 0, use WHERE PriceB 99.99 to protect these items.
GL, Tom
"Kris" wrote:
Tom, Any chance you have a query that would do what I'm looking for? Thanks!
Kris,
Backup First!!
Changes all Items to no discount if on sale
UPDATE Item SET ItemNotDiscountable = 1 WHERE SalePrice 0 AND CONVERT(datetime, CONVERT(nvarchar, SaleStartDate, 101), 101) CONVERT(datetime, CONVERT(nvarchar, GETDATE(), 101), 101)
Backup First!!
Changes all items to discountable while not on sale
UPDATE Item SET ItemNotDiscountable = 0 WHERE CONVERT(datetime, CONVERT(nvarchar, SaleStartDate, 101), 101) > CONVERT(datetime, CONVERT(nvarchar, GETDATE(), 101), 101) OR CONVERT(datetime, CONVERT(nvarchar, SaleEndDate, 101), 101)
Thanks Jeff, works great!
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.