Sunday January 21st 2018

Categories

Insider

Archives

Practicality of Floor function in SQL Server

We are writing this post to share our experience of usefulness of Floor function. We use the floor function in calculating product manufactured and raw material wastage in the process. The problem was to calculate how many units of a product can be manufactured from certain weight of a raw material.

We were getting answer like 8.67 products manufactured from 50 kgs of raw material. We talked to the manufacturing department they confirmed that 8 no of products are manufactured and 0.67 is the wastage. We calculated the weight of each product and then multiply by 8 which gave us the weight in kgs of 8 no of products.
After that weight of 1 product was multiplied with .67 of product and got the weight of wastage in kgs.
In the above scenario we needed the decimal value of .67 separated from 8.67 in separate column to show wastage. Our data was in SQL Server, we utilized the floor function to make 8.67 into 8 and then subtracted the 8 from 8.67. The issue was resolved by using floor function.

Below is the related information about Floor function as obtained from SQL Server books Online.

CEILING and FLOOR

The CEILING function returns the smallest integer greater than or equal to the given numeric expression. The FLOOR function returns the largest integer less than or equal to the given numeric expression. For example, given a numeric expression of 12.9273, CEILING returns 13, and FLOOR returns 12. The return value of both FLOOR and CEILING has the same data type as the input numeric expression.