TSQL Challenges

TSQL Challenge 11 – Calculate the lowest price of an Item by applying the best combination of discount coupons

We hope you enjoyed the previous challenge and it is time to look at the next challenge. We are very excited to introduce TSQL Challenge #11, which we think all of you will enjoy solving. Just like we did with the previous challenge, there will be a FREE training webcast for those people who find it hard to solve. If you would like to attend the training webcast, please register at http://tsqlchallenge11.eventbrite.com/.

If you have comments, questions, challenge ideas or problems that you think should be interesting to discuss, don’t hesitate to post them on the dedicated forum threads or write to us.

The Context

The context of this challenge is about dealing with combinations and how to extract valuable data. For this challenge we will work with two data tables.

The products with their prices:

ID NAME    PRICE
-- ------- ---------
1  PROD 1  100,00
2  PROD 2  220,00
3  PROD 3  15,00
4  PROD 4  70,00
5  PROD 5  150,00

And the discount coupons:

ID NAME         VALUE  IS_PERCENT
-- -----------  ------ ----------
1  CP 1 : -15$  15     0
2  CP 2 : -5$   5      0
3  CP 3 : -10%  10     1
4  CP 4 : -12$  12     0

On the IS_PERCENT column, you should understand, if 0 value is considered as real money value, if 1 then it is a percent value of the price.

The Challenge

For this shopping application, customers could use one to two coupons for the same product but the discount price can not be less than 70% of the original price and the total amount of the discount can not exceed 30$.

It is important to note that coupons are applied in a cumulative way. The second coupon is applied on the result of the original price + first coupon.

With these conditions, the boss ask for a report that shows for each product the minimum price that should be paid for a product using any combination of the discount coupons. For this report you need also to show:

• Original price (PRICE)
• Discount price (DISC_PRICE)
• Total amount of the discount (TOT_DISC)
• Total rate of the discount (RATE)
• An info field with names of coupons applied (COUPON_NAMES)

Regarding the original data tables, here is the final report you should produce:

ID NAME    PRICE    DISC_PRICE  TOT_DISC  RATE    COUPON_NAMES
-- ------  -------- ----------- --------- ------- -------------------------
1  PROD 1  100.00$  73.00$      27.00$    27.00%  CP 4 : -12$ + CP 1 : -15$
2  PROD 2  220.00$  193.00$     27.00$    12.27%  CP 4 : -12$ + CP 1 : -15$
3  PROD 3  15.00$   13.50$      1.50$     10.00%  CP 3 : -10%
4  PROD 4  70.00$   49.50$      20.50$    29.28%  CP 1 : -15$ + CP 3 : -10%
5  PROD 5  150.00$  120.00$     30.00$    20.00%  CP 3 : -10% + CP 1 : -15$

Sample Data

As usual, you should use exactly the sample data as it is provided in your solution script:

DECLARE @T TABLE (ID INT IDENTITY, NAME NVARCHAR(20),PRICE MONEY)
INSERT INTO @T (NAME,PRICE) VALUES ('PROD 1',100) 
INSERT INTO @T (NAME,PRICE) VALUES ('PROD 2',220) 
INSERT INTO @T (NAME,PRICE) VALUES ('PROD 3',15) 
INSERT INTO @T (NAME,PRICE) VALUES ('PROD 4',70) 
INSERT INTO @T (NAME,PRICE) VALUES ('PROD 5',150) 


DECLARE @C TABLE (ID INT IDENTITY, NAME NVARCHAR(20), VALUE INT, IS_PERCENT BIT) 
INSERT INTO @C (NAME,VALUE,IS_PERCENT) VALUES ('CP 1 : -15$',15,0) 
INSERT INTO @C (NAME,VALUE,IS_PERCENT) VALUES ('CP 2 : -5$',5,0) 
INSERT INTO @C (NAME,VALUE,IS_PERCENT) VALUES ('CP 3 : -10%',10,1) 
INSERT INTO @C (NAME,VALUE,IS_PERCENT) VALUES ('CP 4 : -12$',12,0)

Notes

1. As usual, write a single query that produces the expected output.
2. You should target any version of Sql Server
3. Send your entries to tc@beyondrelational.com
4. Do not include your solutions in the body of the email. Send them as an attachment in the email. Name it “firstname_lastname.sql”.
5. Add ‘TSQL Challenge #11’ in the subject line of the email.
6. Last date to submit your entries: 27 July 2009
7. Use this forum for any questions related to TSQL Challenge #11