CASE with FLOOR function : FLOOR « Math Functions « SQL Server / T-SQL Tutorial

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

11.10.FLOOR
	11.10.1.	FLOOR returns the value that represents the largest integer that is less-than or equal to the input_number.
	11.10.2.	Computing the modulus with FLOOR.
	11.10.3.	Returning the decimal portion using FLOOR.
	11.10.4.	select FLOOR(-1.25) (minus)
	11.10.5.	select FLOOR(1.25)
	11.10.6.	MIN(FLOOR(price)),
	11.10.7.	CASE with FLOOR function