June 5, 2012 at 2:33 pm
Comparing the practice rate to the Target rate.
This is to determine the Red or Green highlighting in the Crystal report.
UPDATE ##CG_Results_for_Report --------IS FINE
SET CompareRate = 1 ( green)
Where Prac_Rate >= A_NatlRate
UPDATE ##CG_Results_for_Report -------------IS FINE
SET CompareRate = -1 (red)
WHERE Prac_Rate < A_75NatlRate
I have facing problem with one practice rate which is printing in green supposed to print in black because A_NatlRate and A_75NatlRate are each 0.0
how to incorporate that in sql in
UPDATE ##CG_Results_for_Report
SET CompareRate = 0 --BLACK
then what????
Thanks for the help in advance.
June 5, 2012 at 6:13 pm
have facing problem with one practice rate which is printing in green supposed to print in black because A_NatlRate and A_75NatlRate are each 0.0
how to incorporate that in sql in
UPDATE ##CG_Results_for_Report
SET CompareRate = 0 --BLACK
then what ????
You almost wrote it out correctly in your description...
UPDATE ##CG_Results_for_Report
SET CompareRate = 0 --BLACK
WHERE A_NatlRate = 0 AND A_75NatlRate = 0
You could also use a CASE statement and knock all of this out in a single pass instead of 3.
UPDATE ##CG_Results_for_Report
SET CompareRate =
CASE
WHEN A_NatlRate = 0 AND A_75NatlRate = 0 THEN 0 --BLACK
WHEN Prac_Rate >= A_NatlRate THEN 1 --Green
WHEN Prac_Rate < A_75NatlRate THEN -1 --Red
END
;
Of much larger concern is the fact that you've used a global temp table which may cause problems for conncurrent runs.
--Jeff Moden
June 6, 2012 at 7:51 am
Starnge thing is happening, all turnig to BLACK!! after I apply the CASE statement.
June 6, 2012 at 8:08 am
hbanerje (6/5/2012)
I have facing problem with one practice rate which is printing in green supposed to print in black because A_NatlRate and A_75NatlRate are each 0.0
how to incorporate that in sql in
UPDATE ##CG_Results_for_Report
SET CompareRate = 0 --BLACK
then what????
What condition must occur for BLACK? Prac_Rate = 0?
June 6, 2012 at 9:14 am
Hi,
your code did ask to make it green if Prac_Rate is equal or greater than A_NatlRate (Where Prac_Rate >= A_NatlRate) so that was quite right.
The code using the CASE statement should work just fine.
Although I am a bit confused by the use of the different columns to determine the value of your indicator (although there's nothing wrong with it perse)
You need to debug your values obviously ...
have you done a
SELECT Prac_Rate, A_NatlRate, A_75NatlRate
FROM ##CG_Results_for_Report;?
Just to get an idea of what it is you are looking at? there may be a bug in the calculation of those values.
Re using the global temp table - I agree, why not a simple #temp table used within that session?
HTH,
B
June 6, 2012 at 10:38 am
Black - when the numbers are in between for example the numbers should be in BLACK when it is less than A_NatlRate and greater than A_75NatlRate and when A_NatlRate and A_75NatlRate =0
June 6, 2012 at 11:48 am
hbanerje (6/6/2012)
Black - when the numbers are in between for example the numbers should be in BLACK when it is less than A_NatlRate and greater than A_75NatlRate and when A_NatlRate and A_75NatlRate =0
That's certainly different than what you asked for originally. Just add the other conditions for read and green to the condition for black using AND.
This also show that you may have an additional problem beause it showed that all the values in your table have a value of 0 for the entire A_NatlRate and A_75NatlRate columns.
--Jeff Moden
June 6, 2012 at 12:16 pm
Could you please give me the query? did not understand quite right.
June 7, 2012 at 8:01 am
can you please clarify? Thanks.
June 7, 2012 at 5:11 pm
Yes... clarification. Look at the WHENs and ANDs in my previous code. Now, look at the "Whens and Ands" in your description.
when it is
less than A_NatlRate and greater than A_75NatlRate and when A_NatlRate and A_75NatlRate =0
Now... make the leap from your own description to writing your own code. Give it a shot. Just drop the sencond "when" in your description above and you should be golden.
I know you can do this and that's why I'm not writing the actual code for you. Swim!!! 🙂
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply