SQL Server/T-SQL Tutorial/Math Functions/FLOOR
Содержание
CASE with FLOOR function
<source lang="sql">
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</source>
Computing the modulus with FLOOR.
<source lang="sql">
4> 5> 6> SELECT 5-2*FLOOR(5/2) 7> GO
1
(1 rows affected) 1></source>
FLOOR returns the value that represents the largest integer that is less-than or equal to the input_number.
<source lang="sql">
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></source>
MIN(FLOOR(price)),
<source lang="sql">
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</source>
Returning the decimal portion using FLOOR.
<source lang="sql">
4> 5> 6> SELECT 1.22-FLOOR(1.22) 7> GO
.22
(1 rows affected) 1></source>
select FLOOR(1.25)
<source lang="sql">
2> select FLOOR(1.25) 3> GO
1
(1 rows affected) 1></source>
select FLOOR(-1.25) (minus)
<source lang="sql">
3> select FLOOR(-1.25) 4> GO
-2
(1 rows affected)</source>
