Share via

T-SQL Code Anderson-Darling Calculation

Dan W 0 Reputation points
2023-02-02T16:02:55.7+00:00

I am following an article posted on sql server central on the Anderson-darling calculation.

https://www.sqlservercentral.com/blogs/goodness-of-fit-testing-with-sql-server-part-7-3-the-anderson-darling-test

The code has worked, but is out by 0.001. I have used two programs, MiniTab and Excel Workbook to compare answers and they get a result of 5.808, but i am getting 5.807. Excel book

I have looked over the code and even set the number of decimal places down, but still nothing.

I know within the function that the calculation returned is not correct, slightly off.

any ideas why this calculation is out and how to fix the SQL to match that of MiniTab & the Excel Workbook

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2023-02-02T22:50:17.7633333+00:00

    I am not familiar with Anderson-Darling, but when I see a difference of 1 in the last decimal, I am inclined to attribute that to rounding. I took a quick look at the article, and I see that the computation uses floats. With floats this sort of rounding issues are very common.

    Say that the exact number is is 5.8075. When rounded to three decimals, this should be 5.808. However, floats are approximate numbers. Or more precisely, they are exact in the base of 2, but on in the base of 10. So the number 5.8075 may not be possible to represent in a float, but it could be 5.807500000000001 or 5.8074999999999999999, depending on how the calculation actually ran. And for this reason, you can get difference results in Excel and in T-SQL.

    In short: you should not worry about this small difference.

    0 comments
  2. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-02-03T07:15:44.29+00:00

    Hi @Dan W

    I tried to look at the link you shared. I agree with Erland that floats have rounding issues.

    For example, although 2.50 is considered FLOAT, you might find that the number is not fully represented as 2.50 in memory. Instead, you may see a digital output of 2.499999.

    I'm not sure if that's the cause of your problem.

    Best regards,

    Percy Tang

    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments