Working with data these days seems to be all about metrics, measurements, and numbers. Healthcare data of course is no exception, just think of the many different contexts of numeric values possible in the MEDITECH EHR: lab test results, patient counts, drug dosages, account balances and budgets. Fortunately, SQL Server gives us some tools to work with and manipulate numeric values to the sometimes very precise formats we need in a clinical context.

Thanks to Acmeware consultants Geoff Grouten and Joe Adams for this blog content!

Using the ROUND Function

We often need to round whole or decimal numbers to a certain length. While we think in terms of rounding up or down, when using the T-SQL ROUND system function, we have to think a little more specifically: to what length (or precision) do we want to round (the number of decimal places), and do we want to round the number to the specified length, or merely truncate any numbers beyond it? Here's the syntax of the ROUND function, directly from Microsoft:

ROUND ( numeric_expression , length [ ,function ] )

The ROUND function takes two or three arguments: the numeric_expression is the value you're working with (typically, a field name or calculated value from your dataset) and is required; length specifies the precision to which you wish to round the numeric_expression relative to a decimal point and can be specified as a positive or negative number. Finally, the optional function argument is specified as 0 or 1 (not including this third argument is the same as specifying 0). When you don't include it or use 0, the numeric_expression is rounded; when you use 1, it is truncated at the specified length.

Look at how our numeric_expression of 1.526 is represented based on the length and function, you can see the difference between rounding and truncating quite clearly. An example from a client of ours is from pharmacy medication dosages and dispensing forms: a patient is prescribed a total of 4.5mg of melatonin. The melatonin tablets are 3mg each, so how many should be given? If we're not careful with the ROUND function and don't specify the correct length, we can wind up rounding to 2, when in fact we should give 1.5 tablets for the correct total dosage.

The Float and Decimal Data Types

When working with numbers in SQL Server we should always keep in mind the underlying data types for table columns, or those that we define when using system or user-defined functions. Two numeric data types that we see often in use by Data Repository tables are float and decimal. These two data types are similar in that we can use them when we need to calculate or otherwise interact with non-whole numbers (that is, numbers with decimals). However they behave very differently by design in terms of precision: float values are approximate, whereas the decimal data type (which is synonymous with the numeric data type) is exact, in that you can specify the precision and scale. (For more on these and other data types in SQL Server, see Microsoft's Learn pages.)

In the sample, we declare four variables: the numbers 0.1 and 0.2, but doing so as both float and decimal data types. When we add 0.1 and 0.2 together as both float and decimal datatypes, they appear to return the same value (0.3). However, using a CASE statement to determine if each set of variables added together returns a value of 0.3, in the case of float, it does not. To our eyes it does, but behind the scenes in SQL it does not, because the underlying data type is approximate.

So why use the float data type at all? In theory, what it lacks in precision, it can accommodate a greater range of possible values than decimal. In practice however, we'll never encounter the extreme ranges of those values in a MEDITECH environment, so it's safe to avoid the float data type. (For an excellent and more technical discussion on this topic, see the Learn SQL blog.)