Clever Geek Handbook

The sum of negative and positive numbers

I am using SQL Server 2008. I have sales to customers and I want to compare them with what the customer bought last year, also calculate the growth. Everything works fine, but once a customer has a negative result for one month, they return the wrong data for goal and growth.

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

code:

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
+3


source to share


3 answers


Assuming you want TARGET = 16, GROWTH = 16, then this should do it:

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


However, I'm really not sure what GROWTH is there - not sure why you are dividing SalesLastYearMonth. It works in the case above, but if SalesLastYearMonth is -4 divisible by 4, what do you want?

0


source


You are dividing by a negative number:

SalesLastYearMonth = -1


You need to either check it or return an absolute value.

0


source


Just a small suggestion. try using ISNULL () function. if the selected value is NULL, you can provide a default return value. As far as I know, it is much faster than CASE WHEN.

0


source






More articles:


All Articles