Mastering the Teradata CEILING Function

sql3

Introduction

To round up decimal values in Teradata, use the CEILING function. It is efficient and accurate. This blog post will show how to use the CEILING function in Teradata to simplify data processing tasks.

Understanding the Teradata CEILING Function

The CEILING function in Teradata is a built-in mathematical function that takes a numeric value as input and returns the smallest integer value greater than or equal to the input value. In other words, the CEILING function rounds up the input value to the nearest whole number. The syntax for the CEILING function is as follows:

CEILING(numeric_value)

Basic Usage of CEILING

To demonstrate the basic usage of the CEILING function, let's look at a few examples:

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.

Get the next issue

Rounding up a positive decimal value: SELECT CEILING(7.12) AS rounded_value; This query will return the value '8', as it is the smallest integer greater than or equal to 7.12.
Rounding up a negative decimal value: SELECT CEILING(-7.12) AS rounded_value; This query will return the value '-7', as it is the smallest integer greater than or equal to -7.12.
Rounding up a whole number: SELECT CEILING(5) AS rounded_value; This query will return the value '5', as it is already a whole number.

Using CEILING with Columns and Tables

In practice, you'll often use the CEILING function to round up decimal values stored in columns within your tables. For example, suppose you have a table called 'sales' with a column named 'revenue' that contains decimal values. To round up these values to the nearest whole number, you can use the following query:

Get the next issue by email.

SELECT CEILING(revenue) AS rounded_revenue FROM sales;

This query will return the 'revenue' column values rounded up to the nearest whole number.

Combining CEILING with Other Functions

The CEILING function can be combined with other functions in Teradata to perform more complex calculations. For example, you might want to calculate the total revenue for a specific time period and round up the result. To do this, you can use the following query:

SELECT CEILING(SUM(revenue)) AS total_rounded_revenue FROM sales WHERE sales_date BETWEEN '2023-01-01' AND '2023-12-31';

This query calculates the total revenue for the specified date range and rounds up the result to the nearest whole number.

Conclusion

Teradata's CEILING function simplifies numerical data processing tasks and ensures accurate results. Understand its various uses to enhance data analysis and reporting capabilities. Experiment with it and other Teradata math functions to unlock full data management potential.

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.