How to make it black

Question

How to make it black

hbanerje

SSC Eights!

Points: 818
More actions
June 5, 2012 at 2:33 pm

#270251

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
SETCompareRate= 1 ( green)
Where Prac_Rate >= A_NatlRate
UPDATE##CG_Results_for_Report -------------IS FINE
SETCompareRate= -1 (red)
WHEREPrac_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
SETCompareRate= 0 --BLACK
then what????
Thanks for the help in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hbanerje SSC Eights! Points: 818 More actions · Answer 2

Starnge thing is happening, all turnig to BLACK!! after I apply the CASE statement.

KarenM SSChasing Mays Points: 608 More actions · Answer 3

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
SETCompareRate= 0 --BLACK
then what????

What condition must occur for BLACK? Prac_Rate = 0?

bleroy Hall of Fame Points: 3052 More actions · Answer 4

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

hbanerje SSC Eights! Points: 818 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hbanerje SSC Eights! Points: 818 More actions · Answer 7

Could you please give me the query? did not understand quite right.

hbanerje SSC Eights! Points: 818 More actions · Answer 8

hbanerje

SSC Eights!

Points: 818

June 7, 2012 at 8:01 am

#1498186

can you please clarify? Thanks.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)