· 4 years ago · Jun 23, 2021, 01:22 AM
1--Problem Number 1
2/*
3
4We are running a specific discount scheme in our Northwind Organization.
5Every month the owner of the company will choose a category and we will provide a discount to that category products.
6If the owner chooses 'Beverages' as the category, we will provide a discount to products of 'Beverages' categories.
7So, the category name is the input parameter.
8
9
10
11--check if an old version of this procedure exists and delete it if it does
12IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = 'sp_MonthlyDiscount')
13 DROP PROCEDURE sp_MonthlyDiscount;
14GO
15
16--create the procedure
17CREATE PROCEDURE sp_MonthlyDiscount
18
19@discountCategory nvarchar(15)--input parameter for category of discounted items
20AS
21 BEGIN
22
23 IF @discountCategory NOT IN (SELECT CategoryName FROM Categories)--quit if the provided category isn't a valid category
24 BEGIN
25 RETURN -1
26 END
27
28 --reset the unit price to default for every item in the category
29 UPDATE [Order Details]
30 SET [Order Details].UnitPrice = Products.UnitPrice
31 FROM [Order Details], Products
32 WHERE [Order Details].ProductID = Products.ProductID
33 AND [Order Details].ProductID IN
34 (SELECT [Order Details].ProductID
35 FROM [Order Details]
36 JOIN Products ON [Order Details].ProductID = Products.ProductID
37 JOIN Categories ON Products.CategoryID = Categories.CategoryID
38 WHERE Categories.CategoryName = @discountCategory )
39
40 --add a 5% discount to every order that has a value between 0 and 50
41 UPDATE [Order Details]
42 SET Discount = Discount + .05
43 WHERE ProductID IN
44 (SELECT [Order Details].ProductID
45 FROM [Order Details]
46 JOIN Products ON [Order Details].ProductID = Products.ProductID
47 JOIN Categories ON Products.CategoryID = Categories.CategoryID
48 WHERE Categories.CategoryName = @discountCategory )
49
50 AND ([Order Details].Quantity * [Order Details].UnitPrice) BETWEEN 1 AND 49;
51
52
53
54
55
56 --add a 10% discount to every order that has a value between 50 and 99
57 UPDATE [Order Details]
58 SET Discount = Discount + .1
59 WHERE ProductID IN
60 (SELECT [Order Details].ProductID
61 FROM [Order Details]
62 JOIN Products ON [Order Details].ProductID = Products.ProductID
63 JOIN Categories ON Products.CategoryID = Categories.CategoryID
64 WHERE Categories.CategoryName = @discountCategory )
65
66 AND ([Order Details].Quantity * [Order Details].UnitPrice) BETWEEN 50 AND 99;
67
68
69 --add a 15% discount to every order that has a value greater than 100
70 UPDATE [Order Details]
71 SET Discount = Discount + .15
72 WHERE ProductID IN
73 (SELECT [Order Details].ProductID
74 FROM [Order Details]
75 JOIN Products ON [Order Details].ProductID = Products.ProductID
76 JOIN Categories ON Products.CategoryID = Categories.CategoryID
77 WHERE Categories.CategoryName = @discountCategory )
78
79 --scenario doesn't give rules for ordertotal == 100 so i'll assume 100 is included in the higher discount like 50 was
80 AND ([Order Details].Quantity * [Order Details].UnitPrice) >= 100;
81
82
83 -- update the Unit Price in the table for each affected discount
84 UPDATE [Order Details]
85 SET UnitPrice = UnitPrice - (UnitPrice * Discount)
86 WHERE ProductID IN
87 (SELECT [Order Details].ProductID
88 FROM [Order Details]
89 JOIN Products ON [Order Details].ProductID = Products.ProductID
90 JOIN Categories ON Products.CategoryID = Categories.CategoryID
91 WHERE Categories.CategoryName = @discountCategory )
92
93
94 END
95
96GO
97 /*Demonstration for problem 1 */
98
99 --Get orders before discount
100 SELECT * FROM [Order Details]
101
102 --apply discount
103
104EXEC sp_MonthlyDiscount
105@discountCategory = 'Beverages'
106GO
107 --and after discount
108 SELECT * FROM [Order Details]
109
110 --Seems to be working great. Only wish there was an easy way to reset the table after applying those discounts without resetting the DB. I'm already up to 65% in some of the orders
111*/
112