SQL Server 百分比计算未正确计算 [英] SQL Server Percentage calculation not calculating correctly

问题描述

除了 OrderCountPercentage 计算之外，我有这个 SQL 语句可以正常工作.我不明白为什么它不计算这些，因为公式是正确的.我只能认为它与使用 COUNT 函数的事实有关.任何帮助表示赞赏.

I have this SQL statement that works fine other than the OrderCountPercentage calculation. I cannot understand why it is not calculating these as the formula is correct. I can only think its to do with the the fact this is using the COUNT function. Any help appreciated.

SELECT Table2014.OrderDate                                                                                AS December2014OrderDate,
       ISNULL(Table2014.Total, 0)                                                                         AS December2014DailySales,
       ISNULL(Table2013.Total, 0)                                                                         AS December2013DailySales,
       ISNULL(Table2014.Total, 0) - ISNULL(Table2013.Total, 0)                                            AS DailySalesDifference,
       ( ISNULL(Table2014.Total, 0) - ISNULL(Table2013.Total, 0) ) / NULLIF(Table2013.Total, 0) * 100     AS SalesPercentage,
       ISNULL(Table2014.OrderCount, 0)                                                                    AS December2014DailyOrderCount,
       ISNULL(Table2013.OrderCount, 0)                                                                    AS December2013DailyOrderCount,
       ISNULL(Table2014.OrderCount, 0) - ISNULL(Table2013.OrderCount, 0)                                  AS DailyOrderCountDifference,
       ( ISNULL(Table2014.OrderCount, 0) - ISNULL(Table2013.OrderCount, 0) ) / Table2013.OrderCount * 100 AS Percentage
FROM   (SELECT Sum(order_header_total.oht_net)                           AS Total,
               Dateadd(DAY, 0, Datediff(D, 0, order_header.oh_datetime)) AS OrderDate,
               Count(order_header.oh_order_number)                       AS OrderCount
        FROM   dbo.order_header_total
               INNER JOIN dbo.order_header
                       ON order_header_total.oht_oh_id = order_header.oh_id
        WHERE  order_header.oh_datetime BETWEEN '12/01/2014 00:00:00' AND '12/31/2014 23:59:59'
               AND order_header.oh_os_id IN ( 1, 6, 4 )
               AND order_header.oh_cd_id = 76
        GROUP  BY Dateadd(DAY, 0, Datediff(D, 0, order_header.oh_datetime))) Table2014
       LEFT OUTER JOIN (SELECT Sum(order_header_total.oht_net)                                             AS Total,
                               Dateadd(YEAR, 1, Dateadd(DAY, 0, Datediff(D, 0, order_header.oh_datetime))) AS OrderDate,
                               Count(order_header.oh_order_number)                                         AS OrderCount
                        FROM   dbo.order_header_total
                               INNER JOIN dbo.order_header
                                       ON order_header_total.oht_oh_id = order_header.oh_id
                        WHERE  order_header.oh_datetime BETWEEN '12/01/2013 00:00:00' AND '12/31/2013 23:59:59'
                               AND order_header.oh_os_id IN ( 1, 6, 4 )
                               AND order_header.oh_cd_id = 76
                        GROUP  BY Dateadd(YEAR, 1, Dateadd(DAY, 0, Datediff(D, 0, order_header.oh_datetime)))) Table2013
                    ON Table2013.OrderDate = Table2014.OrderDate
ORDER  BY Table2014.OrderDate 

推荐答案

问题可能是整数除法.我假设您指的是:

The problem is probably integer division. I assume you are referring to:

 (ISNULL(Table2014.OrderCount, 0) - ISNULL(Table2013.OrderCount, 0)) / Table2013.OrderCount * 100 AS Percentage

如果所有这些都是整数，则除法是一个整数.我通常通过乘以 1.0 来解决这个问题.但也许更正式的方法是将其中一个值转换为浮点数或数字:

If all these are integers, the division is an integer. I usually solve this by multiplying by 1.0. But perhaps a more formal way is to convert one of the values to a float or numeric:

 (ISNULL(Table2014.OrderCount, 0) - ISNULL(Table2013.OrderCount, 0)) / cast(Table2013.OrderCount as float) * 100 AS Percentage

这篇关于SQL Server 百分比计算未正确计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！