Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gheffern
Starting Member
1 Post |
 Posted - 2005-11-14 : 19:41:53
|
| I have two tables: Discounts and Criteria. The Discount table has DiscountName and Amount. Criteria has several entries per each discount that have to match for a discount to be applied - CriteriaName and CriteriaValue - and the ID of the corresponding Discount. This means I need to come up with a way to process each Discount with its one or more criteria one at a time? To make it more difficult, the criteria can be different for each discount.10% Off Web Orders15% Off Web Orders - Las Vegas20% Off Web Orders - Alaska - Over $299Above are the three scenarios I am testing. Each has one entry in the Discount table. Below are the entries in the Criteria table.10% Off Web Orders - CriteriaName = Source / CriteriaValue = Web15% Off Web Orders - Las VegasCriteriaName = Source / CriteriaValue = WebCriteriaName = Location / CriteriaValue = Las Vegas15% Off Web Orders - Las VegasCriteriaName = Source / CriteriaValue = WebCriteriaName = Location / CriteriaValue = AlaskaCriteriaName = Total / CriteriaValue = 305How do I get a list of Discounts and Amounts that should be applied?Thanks for looking this over!Gary |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-14 : 21:54:51
|
| Do us a favor and post like this: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWe can then hopefully help you much quicker. We need some sample data to help you out effectively, with preferrably a good enough sample to let us do what you outlined in your original post.You can use a CASE statement to accomplish what you need to do here. Remember that CASE statements can be used in combination with aggregation functions.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
|
|
|
Do I need a nested dynamic query?