Round Up & Round Down Any Fraction Number

[kapil_kumar8]

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

Input Fraction Number	Expected Result
1.1	2
1.298	2
1.8978	2

Expected Round Down Result

Input Fraction Number	Expected Result
1.1	1
1.298	1
1.8978	1

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/20db6dd575191014b704978452...

FLOOR Function -

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

Hope this will be helpful..!

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

Thanks.!