Geek asks and answers

SQL Server - using invoice amount

I am using SQL Server. The formula I need to use is (Good-Bad) / Total_Responses. Good is when value = = 9, and Bad is <= 6.

The data that I have:

DATE        Q1  Q2
2012-03-04   9   9
2012-03-04   8   8
2012-03-04   7   9
2012-03-04   4  NA
2012-03-04  10  10
2012-03-04   8   3
2012-03-04   3   4
2012-03-04   2   6
2012-03-04   6   8
2012-03-04  NA   6

I know that I will need to use "CASE WHEN ISNUMERIC (Q1) = 1" to make sure it does not use NA values ​​(they are stored as Nvarchar)

Thus, the formula will end up being (5-8) / 18-0-06666 so I try to make the data look like this:

DATE        Promotor_Score
2012-03-04        -0.16666

Thank!

+3
source share
2 answers

Does it do the trick?

SELECT  Date,
        CAST((SUM(CASE WHEN ISNUMERIC(Q1) != 1 THEN 0
                 WHEN CAST(Q1 AS int) >= 9 THEN 1
                 WHEN CAST(Q1 AS int) <= 6 THEN -1
                 ELSE 0 END)
        + SUM(CASE WHEN ISNUMERIC(Q2) != 1 THEN 0
                 WHEN CAST(Q2 AS int) >= 9 THEN 1
                 WHEN CAST(Q2 AS int) <= 6 THEN -1
                 ELSE 0 END)) AS float)
        / (SUM(CASE WHEN ISNUMERIC(Q1) != 1 THEN 0
                   ELSE 1 END)
        + SUM(CASE WHEN ISNUMERIC(Q2) != 1 THEN 0
                   ELSE 1 END))
FROM    Questions
GROUP BY Date

Or, if "NA" is the only non-numeric value, check it explicitly.

+4
source

@DavidM, float- Q2:

SELECT [date], 
1e0 -- a float multiplier to avoid integer value
* SUM( 0
    -- get the positive, negative or neutral from q1
    + CASE WHEN ISNUMERIC(q1) != 1 THEN  0
    WHEN CAST(q1 AS int) >= 9    THEN  1
    WHEN CAST(q1 AS int) <= 6    THEN -1
    ELSE 0 END
    -- get the positive, negative or neutral from q2
    + CASE WHEN ISNUMERIC(q2) != 1 THEN  0
    WHEN CAST(q2 AS int) >= 9    THEN  1
    WHEN CAST(q2 AS int) <= 6    THEN -1
    ELSE 0 END
)
/ SUM( 0
    -- get the number of valid questions from q1
    + CASE WHEN ISNUMERIC(q1) != 1 THEN 0 ELSE 1 END        
    -- get the number of valid questions from q2
    + CASE WHEN ISNUMERIC(q2) != 1 THEN 0 ELSE 1 END
)
FROM Questions
GROUP BY Date;
0

More articles:


All Articles