SQL Server/T-SQL Tutorial/Math Functions/FLOOR

CASE with FLOOR function

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

Computing the modulus with FLOOR.

4>
5>
6> SELECT 5-2*FLOOR(5/2)
7> GO
-----------
          1
(1 rows affected)
1>

FLOOR returns the value that represents the largest integer that is less-than or equal to the input_number.

The syntax for the FLOOR function is as follows: FLOOR(input_number)
9> SELECT 5-2*FLOOR(5/2)
10> GO
-----------
          1
(1 rows affected)
1>

MIN(FLOOR(price)),

3> CREATE TABLE titles(
4>    title_id       varchar(20),
5>    title          varchar(80)       NOT NULL,
6>    type           char(12)          NOT NULL,
7>    pub_id         char(4)               NULL,
8>    price          money                 NULL,
9>    advance        money                 NULL,
10>    royalty        int                   NULL,
11>    ytd_sales      int                   NULL,
12>    notes          varchar(200)          NULL,
13>    pubdate        datetime          NOT NULL
14> )
15> GO
1>
2> insert titles values ("1", "Secrets",   "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The",       "business",     "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology",   "0736", $7.99,  $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology",   "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With",      "business",     "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley",    "mod_cook",     "0877", $19.99, $0.00,    12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?",      "trad_cook",    "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty",     "trad_cook",    "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT "Low End"=MIN(FLOOR(price)),
4>     "High End"=MAX(CEILING(price))
5> FROM titles
6> GO
Low End               High End
--------------------- ---------------------
               7.0000               20.0000
(1 rows affected)
1>
2> drop table titles;
3> GO

Returning the decimal portion using FLOOR.

4>
5>
6> SELECT 1.22-FLOOR(1.22)
7> GO
--------
     .22
(1 rows affected)
1>

select FLOOR(1.25)

2> select FLOOR(1.25)
3> GO
-----
    1
(1 rows affected)
1>

select FLOOR(-1.25) (minus)

3> select FLOOR(-1.25)
4> GO
-----
   -2
(1 rows affected)