3> CREATE TABLE Products (
4> ProductID int NOT NULL ,
5> ProductName nvarchar (40) NOT NULL ,
6> SupplierID int NULL ,
7> CategoryID int NULL ,
8> QuantityPerUnit nvarchar (20) NULL ,
9> UnitPrice money NULL,
10> UnitsInStock smallint NULL,
11> UnitsOnOrder smallint NULL,
12> ReorderLevel smallint NULL,
13> Discontinued bit NOT NULL
14> )
15> GO
1> INSERT Products VALUES(1,'F',15,4,'10 - 999 g pkgs.',61.5,66,6,6,6)
2> INSERT Products VALUES(2,'M',14,4,'24 - 888 g pkgs.',34.8,74,7,7,7)
3> INSERT Products VALUES(3,'R',17,8,'24 - 777 g jars',17,171,0,5,0)
4> INSERT Products VALUES(4,'L',4,7,'5 kg pkg.',10,4,20,5,0)
5> INSERT Products VALUES(5,'R',12,1,'24 - 0.5 l bottles',1.23,445,0,25,0)
6> INSERT Products VALUES(6,'L',23,1,'500 ml',18,57,1,20,0)
7> INSERT Products VALUES(7,'O',12,2,'12 boxes',13,23,0,15,0)
8> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE PROC spMarkupTest
3> @MarkupAsPercent money
4> AS
5> DECLARE @Multiplier money
6> SELECT @Multiplier = @MarkupAsPercent / 100 + 1
7> SELECT TOP 10 ProductId, ProductName, UnitPrice,
8> UnitPrice * @Multiplier AS "Marked Up Price", "New Price" =
9> CASE WHEN FLOOR(UnitPrice * @Multiplier + .24)
10> > FLOOR(UnitPrice * @Multiplier)
11> THEN FLOOR(UnitPrice * @Multiplier) + .95
12> WHEN FLOOR(UnitPrice * @Multiplier + .5) >
13> FLOOR(UnitPrice * @Multiplier)
14> THEN FLOOR(UnitPrice * @Multiplier) + .75
15> ELSE FLOOR(UnitPrice * @Multiplier) + .49
16> END
17> FROM Products
18> ORDER BY ProductID DESC
19>
20> GO
1>
2> EXEC spMarkupTest 10
3>
4> drop PROC spMarkupTest;
5>
6>
7> drop table Products;
8> GO
ProductId ProductName UnitPrice Marked Up Price New Price
----------- ---------------------------------------- --------------------- --------------------- ----------------------
7 O 13.0000 14.3000 14.4900
6 L 18.0000 19.8000 19.9500
5 R 1.2300 1.3530 1.4900
4 L 10.0000 11.0000 11.4900
3 R 17.0000 18.7000 18.7500
2 M 34.8000 38.2800 38.4900
1 F 61.5000 67.6500 67.7500
(7 rows affected)