November 2, 2012 at 3:22 pm
I am having trouble with a calculation. Below is the piece of the query I am inquiring on and below that is the output. In the output, the first line is correct but the next two aren’t. Line two in the complete column should say 100 and line three should say NULL if possible. If there is a zero in Action Required, Complete should be 100 unless there are zero in both. In Excel I have to use conditional formatting to make zero complete red but it's misleading in these instances. The last line is correct.
Sum(NotStart + Pend + Compl) AS 'Total_Items',
Sum(NotStart) AS 'Action Required',
CASE
WHEN Sum(NotStart + Pend + Compl) = 0 THEN 0
ELSE ( Cast(( Cast(Sum(compl) AS NUMERIC) / Nullif(Sum(
NotStart + Pend + Compl),0) ) AS
DECIMAL ( 9, 5)) ) * 100
END AS 'Complete
Total ItemsAction RequiredComplete
1 0 100
3 0 0
0 0 0
4 0 0
November 2, 2012 at 3:25 pm
Since you are asking about the output the best thing to do is post some input and the complete query and expected results for the given input. what i get from your post is that the functions have some sort of grouping that may not be working correctly. however with out any thing to actually test that is about as good as i can answer and even that may not be correct.
Since we can not see what you see we need you to post the data. If you need help on how to post the first link in my signature is an article on what we need to give the best help possible.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 2, 2012 at 4:00 pm
Thanks. I'll need to work on this.
November 2, 2012 at 4:34 pm
What's the difference between line 2 (Total Items: 3) and line 4 (Total Items: 4)? You say line 2 is incorrect and line 4 is correct.
If, as I suppose, line 4 is incorrect as well, I might have understood this. If it's not, I need to know why.
This is a possible guess
Sum(NotStart + Pend + Compl) AS 'Total_Items',
Sum(NotStart) AS 'Action Required',
CASE WHEN Sum(NotStart + Pend + Compl) = 0 AND Sum(NotStart) = 0 THEN 100
WHEN Sum(NotStart + Pend + Compl) = 0 THEN NULL
ELSE ( Cast(( Cast(Sum(compl) AS NUMERIC) /
-- Nullif(Sum( NotStart + Pend + Compl),0) ) AS DECIMAL ( 9, 5)) ) * 100
Sum( NotStart + Pend + Compl) ) AS DECIMAL ( 9, 5)) ) * 100
END AS 'Complete'
Why are you mixing data types in your operations? Are you aware that numeric and decimal are synonyms? Are you aware of the default precision and scale for these data types?
November 2, 2012 at 4:48 pm
You're right. The last line is incorrect. I shouldn't have included it. If it said 4 4 0, it would be correct.
I had it the way you have it without the NULLIF to begin with.
The problem is 0 0 shows as 0 complete but 4 4 also shows as 0 complete. The latter is correct but if 0 0 could just return "100" or "NULL", I would be good on the conditional formatting piece in Excel. I see the NULLIF was making it worse. Also, no, sadly, I don't know too much about data types.
Thank you for your assistance.
November 2, 2012 at 4:58 pm
vicki.k.noble (11/2/2012)
The problem is 0 0 shows as 0 complete but 4 4 also shows as 0 complete. The latter is correct but if 0 0 could just return "100" or "NULL", I would be good on the conditional formatting piece in Excel.
That's why I added an additional condition to the CASE
vicki.k.noble (11/2/2012)
Also, no, sadly, I don't know too much about data types.
You can always learn more.
November 2, 2012 at 5:16 pm
Oh, thank you, yes I see the extra condition (now). Thank you also for the link. I definitely need to study that.
Again, thanks so much for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply