sql - 对负数和正数求和

Question

我正在使用 SQL Server 2008。我有客户的销售额，我想将它与客户去年购买的产品进行比较，并计算增长。一切正常，但一旦客户对某一个月有负数，它就会带回错误的目标和增长数据。

Customer     SalesLastYearMonth      SalesThisYearMonth      Target     Growth
------------------------------------------------------------------------------
abcd         -1                      15                      ????       ???

代码:

SELECT
    Customer,
    CASE 
        WHEN SalesThisYearMonth IS NULL THEN (SalesLastYearMonth * -1)
        WHEN SalesLastYearMonth IS NULL THEN SalesThisYearMonth 
        ELSE SalesThisYearMonth -  SalesLastYearMonth END as Target,
    CASE 
        WHEN SalesThisYearMonth IS NULL THEN -1 
        WHEN SalesLastYearMonth IS NULL THEN 1 
        WHEN SalesThisYearMonth = 0 then -1
        WHEN SalesLastYearMonth = 0 then 1
        ELSE ( SalesThisYearMonth - SalesLastYearMonth) / SalesLastYearMonth END AS Growth

Answer 1

假设您希望 TARGET=16，GROWTH=16，那么应该这样做:

SELECT
    Customer,
    CASE 
        WHEN SalesThisYearMonth IS NULL THEN (SalesLastYearMonth * -1)
        WHEN SalesLastYearMonth IS NULL THEN SalesThisYearMonth 
        ELSE SalesThisYearMonth -  SalesLastYearMonth END as Target,
    CASE 
        WHEN SalesThisYearMonth IS NULL THEN -1 
        WHEN SalesLastYearMonth IS NULL THEN 1 
        WHEN SalesThisYearMonth = 0 then -1
        WHEN SalesLastYearMonth = 0 then 1
        ELSE ( SalesThisYearMonth - SalesLastYearMonth) / ABS(SalesLastYearMonth) END AS Growth

但是，我真的不确定那里的 GROWTH - 不确定为什么要除以 SalesLastYearMonth。在上面的例子中这是有效的，但是如果 SalesLastYearMonth 是 -4，除以 4 是你想要的吗？