T-SQL: Managing Significant Digits

As you work with calculations in SQL, eventually you’re going to get caught up (particularly when trying to output the data to a file, for a vendor) by the T-SQL rules around maintaining a proper number of significant digits in the results of a calculation. Generally speaking, SQL does not want to lose any validity in the data, so it is going to expand the number of significant figures to “keep” everything.

So from code like:

This gets us output like:

From this we see that, depending on how our data is structured, we may get a bunch more significant digits than we expect. Now, we can then reduce those by CAST-ing the results to a specific DECIMAL() or NUMERIC() type with an explicit number of digits, but we can also – when we are using constant integers in the calculation – step around the potential of increasing the number of significant figures.

We do this by using a constant with an implied DECIMAL type, but no explicit significant digits, in the format number followed by a dot followed by… nothing.

The output produced is: 

An odd little bit of functionality, but useful when you need it!

Extra Credit

Grok this chart.

And…

As ever, if you need help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!