# 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

 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/20db6dd575191014b704978452f92bbd.html

FLOOR Function –

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

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

Thanks.!

### Assigned Tags

You must be Logged on to comment or reply to a post.

Thanks Kapil.

Do you know how to make a normal round, like

 1.1 1 1.298 1 1.8978 2

Best regards,
Oleksandr

Kapil Kumar
Blog Post Author

Hi Oleksandr,

You can use ROUND function as used in below Queries.

Hope this Help.

``````SELECT ROUND (1.1) AS "ROUNDED_VALUE", 'INPUT NUMBER - 1.1' AS "INPUT_NUMBER" FROM DUMMY
UNION
SELECT ROUND (1.298) AS "ROUNDED_VALUE", 'INPUT NUMBER - 1.298' AS "INPUT_NUMBER" FROM DUMMY
UNION
SELECT ROUND (1.8978) AS "ROUNDED_VALUE", 'INPUT NUMBER - 1.8978' AS "INPUT_NUMBER" FROM DUMMY;``````

Regards,

Kapil Kumar

Thanks Kapil.

I meant BEx queries. Sorry for my confusion.

Best regards,

Oleksandr

Kapil Kumar
Blog Post Author

Hi Oleksandr,

Sorry, I don't have much expertise in BW. So won't be able to help much regarding BEx queries.

Regards,

Kapil Kumar