Difference calculation not coming out well

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Dear Expert,

Calculation the differences and output not right.

DIFFCQ1C
DIFFCQ2C
DIFFCQ3C
DIFFCQ4C

Please peruse and advice ;

database MS SQL SERVER 2008

Problem area

COALESCE((C.Q1Y2C-A.Q1Y1C) , 0) DIFFCQ1C ,
COALESCE((C.Q2Y2C-A.Q2Y1C) , 0) DIFFCQ2C ,
COALESCE((C.Q3Y2C-A.Q3Y1C) , 0) DIFFCQ3C ,
COALESCE((C.Q4Y2C-A.Q4Y1C) , 0) DIFFCQ4C

SELECT A.BRNNAME,A.SCORE , COALESCE(A.Q1Y1C, 0) Q1Y1C , COALESCE(C.Q1Y2C, 0) Q1Y2C ,
COALESCE(A.Q2Y1C, 0) Q2Y1C , COALESCE(C.Q2Y2C, 0) Q2Y2C ,
COALESCE(A.Q3Y1C, 0) Q3Y1C , COALESCE(C.Q3Y2C, 0) Q3Y2C ,
COALESCE(A.Q4Y1C, 0) Q4Y1C , COALESCE(C.Q4Y2C, 0) Q4Y2C,
COALESCE((C.Q1Y2C-A.Q1Y1C) , 0) DIFFCQ1C ,
COALESCE((C.Q2Y2C-A.Q2Y1C) , 0) DIFFCQ2C ,
COALESCE((C.Q3Y2C-A.Q3Y1C) , 0) DIFFCQ3C ,
COALESCE((C.Q4Y2C-A.Q4Y1C) , 0) DIFFCQ4C
FROM Y1C A
JOIN Y1P B ON A.BRNNAME=B.BRNNAME AND A.SCORE=B.SCORE
JOIN Y2C C ON A.BRNNAME=C.BRNNAME AND A.SCORE=C.SCORE
JOIN Y2P D ON A.BRNNAME=D.BRNNAME AND A.SCORE=D.SCORE
ORDER BY A.BRNNAME

BRNNAME SCORE Q1Y1C Q1Y2C Q2Y1C Q2Y2C Q3Y1C Q3Y2C Q4Y1C Q4Y2C DIFFCQ1C DIFFCQ2C DIFFCQ3C DIFFCQ4C
CAPE COAST DELIGHTED 2 2 5 3 2 2 8 7 0 -2 0 -1
CAPE COAST GOOD 1 1 1 0 0 0 1 1 0 0 0 0
HOHOE DELIGHTED 4 0 0 0 4 5 4 4 0 0 1 0
HOHOE GOOD 2 2 0 0 2 2 2 2 0 0 0 0
HOHOE SATISFACTORY 1 0 1 2 0 0 1 1 0 1 0 0
HOHOE UNHAPPY 1 0 1 1 0 1 1 1 0 0 0 0
KUMASI BAD 1 1 0 0 0 0 1 1 0 0 0 0
KUMASI DELIGHTED 0 0 3 3 3 3 6 6 0 0 0 0
KUMASI GOOD 0 0 1 1 0 0 1 1 0 0 0 0

What I have tried:

These are codes under construction.

Posted 28-Nov-17 1:36am

Member 12770648

Updated 29-Nov-17 5:30am

Add a Solution

Comments

ZurdoDev 28-Nov-17 7:57am

And the problem is?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-11-29T05:30:00

You are using COALESCE at the wrong level.

If you use a NULL value in a calculation then the entire result becomes NULL. What you seem to want is to treat each NULL value as 0 and use the 0 in the calculation.

Here is a very simple example of what I mean

SQL

DECLARE  @table TABLE (Col1 INT NULL, Col2 INT NULL)

INSERT INTO @table (Col1, Col2) VALUES
(NULL, NULL),
(10, NULL),
(NULL, 10),
(20, 5),
(5, 20)

If I want to list the differences between Col1 and Col2 for each row I might start out with something like this:

SQL

SELECT Col1 - Col2 FROM @table

But that will give me the results:

NULL
NULL
NULL
15
-15

Your query is doing the equivalent of:

SQL

SELECT COALESCE(Col1 - Col2,0) FROM @table

which gives the results

My way would be to write the query as:

SQL

SELECT ISNULL(Col1,0) - ISNULL(Col2,0) FROM @table

Results:

So, points to note:
a) I'm coalescing each column individually
b) I've used ISNULL instead of COALESCE. They do exactly the same thing but ISNULL is (apparently) marginally faster when you only have the two items to coalesce. And it is a bit quicker to type :-)

Your query should look something like

SQL

SELECT A.BRNNAME,A.SCORE , COALESCE(A.Q1Y1C, 0) Q1Y1C , COALESCE(C.Q1Y2C, 0) Q1Y2C ,
 COALESCE(A.Q2Y1C, 0) Q2Y1C , COALESCE(C.Q2Y2C, 0) Q2Y2C ,
 COALESCE(A.Q3Y1C, 0) Q3Y1C , COALESCE(C.Q3Y2C, 0) Q3Y2C ,
 COALESCE(A.Q4Y1C, 0) Q4Y1C , COALESCE(C.Q4Y2C, 0) Q4Y2C,
 ISNULL(C.Q1Y2C,0)-ISNULL(A.Q1Y1C,0) DIFFCQ1C ,
 ISNULL(C.Q2Y2C,0)-ISNULL(A.Q2Y1C, 0) DIFFCQ2C ,
 ISNULL(C.Q3Y2C,0)-ISNULL(A.Q3Y1C, 0) DIFFCQ3C ,
 ISNULL(C.Q4Y2C,0)-ISNULL(A.Q4Y1C, 0) DIFFCQ4C 
 FROM Y1C A
 JOIN Y1P B ON A.BRNNAME=B.BRNNAME AND A.SCORE=B.SCORE
 JOIN Y2C C ON A.BRNNAME=C.BRNNAME AND A.SCORE=C.SCORE
 JOIN Y2P D ON A.BRNNAME=D.BRNNAME AND A.SCORE=D.SCORE
 ORDER BY A.BRNNAME

but obviously I haven't been able to test this.