Round Up & Round Down Any Fraction Number

Requirement

Sometime we gets requirement to Round Up\Down any Fraction number to the nearest integer number in HANA View or HANA Sql.

Expected Round Up Result

Expected Round Down Result

Solution

To get the desired output, we can use below mentioned in-built HANA functions.

1. CEIL      – Round Up
2. FLOOR – Round Down

-- Sample Result Set

SELECT CEIL (1.1) FROM DUMMY;
-- Result 2
SELECT CEIL (1.298) FROM DUMMY;
-- Result 2
SELECT CEIL (1.8978) FROM DUMMY;
-- Result 2

SELECT FLOOR (1.1) FROM DUMMY;
-- Result 1
SELECT FLOOR (1.298) FROM DUMMY;
-- Result 1
SELECT FLOOR (1.8978) FROM DUMMY;
-- Result 1

Conclusion

These two functions can help in this special requirement to make a fraction number equivalent to it’s nearest integer number. By using these function we can avoid creating a custom calculation to get the complete integer number for a fraction number.

Reference

CEIL Function –

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.02/en-US/20db6dd575191014b704978452f92bbd.html

FLOOR Function –

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.02/en-US/20e1c2b47519101484d8af631b6845f9.html

Hope this will be helpful..!

Please do let me know if there is any other workaround available.

Thanks.!