Mega Code Archive
CASE with FLOOR function
<![CDATA[
4> CREATE TABLE Products (
5> ProductID int NOT NULL ,
6> ProductName nvarchar (40) NOT NULL ,
7> SupplierID int NULL ,
8> CategoryID int NULL ,
9> QuantityPerUnit nvarchar (20) NULL ,
10> UnitPrice money NULL,
11> UnitsInStock smallint NULL,
12> UnitsOnOrder smallint NULL,
13> ReorderLevel smallint NULL,
14> Discontinued bit NOT NULL
15> )
16> 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> DECLARE @Markup money
3> DECLARE @Multiplier money
4>
5> SELECT @Markup = .10
6> SELECT @Multiplier = @Markup + 1
7>
8> SELECT TOP 10 ProductID, ProductName, UnitPrice,
9> UnitPrice * @Multiplier AS "Marked Up Price", "New Price" =
10> CASE WHEN FLOOR(UnitPrice * @Multiplier + .24)
11> > FLOOR(UnitPrice * @Multiplier)
12> THEN FLOOR(UnitPrice * @Multiplier) + .95
13> WHEN FLOOR(UnitPrice * @Multiplier + .5) >
14> FLOOR(UnitPrice * @Multiplier)
15> THEN FLOOR(UnitPrice * @Multiplier) + .75
16> ELSE FLOOR(UnitPrice * @Multiplier) + .49
17> END
18> FROM Products
19> ORDER BY ProductID DESC
20> 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)
1>
2> drop table Products;
3> GO
]]>