Percentage calculation (1 Viewer)

T

tinyevil777

Registered User.
Local time
Today, 09:01
Joined
Dec 10, 2010
Messages
137
Hello.

After a few hours of tearing my hair out, i'm afraid i have to ask for some of your valuable time in helping me...

I have a table with 3 columns; Site, Live, Target. The Live and Target fields are both integers. I have a stored procedure which creates a temporary table and totals the data. Please see below for an example:

Code: 
Site    Live    Target
0LO      2          2
1BL      1          5
2XA      3          4
3PT      0          0
4FH      0          2
5KL      1          0

What i am TRYING to get is...

Code: 
Site    Live    Target    Conversion
0LO      2          2           100%
1BL      1          5            20%
2XA      3          4           75%
3PT      0          0           ??%
4FH      0          2            0%
5KL      1          0           ??%

What seems like a simple [Live]/[Target]*100 calculation is proving to much for me as i'm having to take into account values of '0'. When i use this simple calculation, it returns a 'cannot divide by zero' error. Very frustrating.

Additionall, for sites 3PT and 5KL, i'm actually not sure what conversion % is mathematically and logically correct... My head is frazzled!

Can someone save me?

Thank you in advance!

Tom.
 
T

tehNellie

Registered User.
Local time
Today, 09:01
Joined
Apr 3, 2007
Messages
751
Code: 
CASE WHEN target > 0 THEN [Live]/[Target]*100 
ELSE 0
END AS conversion

Should do it at a basic level.

3PT I'd say 0. 5KL being infinitely more than target is open to interpretation I suppose.

[edit] Removed test on live, as it's not needed.
 
Last edited:
P

plog

Banishment Pending
Local time
Today, 03:01
Joined
May 11, 2011
Messages
11,638
'Undefined' is the mathematical answer for what you get when you try to divide by 0. To avoid this situation and force an answer always check the denominator to see if its 0 for it prior to doing division:

Conversion: Iif([Target]=0, "N/a", Format([Live]/[Target], "Percent"))

You can change the "N/a" portion to whatever you want to show if Target is 0.
 
T

tinyevil777

Registered User.
Local time
Today, 09:01
Joined
Dec 10, 2010
Messages
137
tehNellie said:
Code: 
CASE WHEN (live > 0 AND target > 0) THEN [Live]/[Target]*100 
ELSE 0
END AS conversion

Should do it at a basic level.

3PT I'd say 0. 5KL being infinitely more than target is open to interpretation I suppose.
Click to expand...

Hello.

I just ran an update query to set the conversion, however it sets the conversion to '0' for all records.

This is the query i used:
Code: 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
UPDATE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] REPConversion [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SET[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Conversion [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CASE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHEN [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]GoneLive [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0 [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Target[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]THEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [GoneLive][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]/[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Target][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]*[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]100 
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ELSE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END
[/COLOR][/SIZE][/COLOR][/SIZE]
 
T

tinyevil777

Registered User.
Local time
Today, 09:01
Joined
Dec 10, 2010
Messages
137
plog said:
'Undefined' is the mathematical answer for what you get when you try to divide by 0. To avoid this situation and force an answer always check the denominator to see if its 0 for it prior to doing division:

Conversion: Iif([Target]=0, "N/a", Format([Live]/[Target], "Percent"))

You can change the "N/a" portion to whatever you want to show if Target is 0.
Click to expand...

Hello,

So if i were to apply the Iif statement into an SQL update query, would the below be a good starting point?

Code: 
UPDATE REPConversion SET Conversion =  Iif([Target]+[GoneLive]=0, "N/a", Format([GoneLive]/[Target], "Percent"))
 
P

plog

Banishment Pending
Local time
Today, 03:01
Joined
May 11, 2011
Messages
11,638
Sorry, I didn't see this was the SQL Server forum--I'm out of my element. My general method is sound, but I'd disregard my specific advice and listen to tehNellie on this one.
 
T

tinyevil777

Registered User.
Local time
Today, 09:01
Joined
Dec 10, 2010
Messages
137
plog said:
Sorry, I didn't see this was the SQL Server forum--I'm out of my element. My general method is sound, but I'd disregard my specific advice and listen to teh on this one.
Click to expand...

That's ok, i appreciate you responding anyway. Just hope someone else can help me...
 
T

tehNellie

Registered User.
Local time
Today, 09:01
Joined
Apr 3, 2007
Messages
751
Sorry Tiny, my bad, I forgot you mentioned the two columns were Integer Values. Because of that the output is an integer hence anything less than 1 is 0 (there is a hierarchy that comes into play if Live and Target were different datatypes to determine what the output will be, but that's not important).

Code: 
SELECT 
CASE WHEN (target > 0) 
  THEN CAST(CAST(Live as decimal)/CAST(target as decimal) *100 as decimal(3,0))
  ELSE 0
END AS conversion

This converts the values to Decimals, performs the calculation and then formats the output to a whole number:

DECIMAL(3,0) forces the output to a maximum of 3 digits, with none to the left of the decimal place.
 
Last edited:
T

tinyevil777

Registered User.
Local time
Today, 09:01
Joined
Dec 10, 2010
Messages
137
tehNellie said:
Sorry Tiny, my bad, I forgot you mentioned the two columns were Integer Values. Because of that the output is an integer hence anything less than 1 is 0 (there is a hierarchy that comes into play if Live and Target were different datatypes to determine what the output will be, but that's not important).

Code: 
SELECT 
CASE WHEN (live > 0) 
  THEN CAST(CAST(Live as decimal)/CAST(target as decimal) *100 as decimal(3,0))
  ELSE 0
END AS conversion

This converts the values to Decimals, performs the calculation and then formats the output to a whole number:

DECIMAL(3,0) forces the output to a maximum of 3 digits, with none to the left of the decimal place.
Click to expand...

Thanks very much for your reply. When i apply your select statement into an Update Query, i get the following error:

Code: 
SQL,
[COLOR=black]UPDATE REPConversion[/COLOR]
[COLOR=black]SET Conversion =CASEWHEN (GoneLive > 0)THENCAST(CAST(GoneLive asdecimal)/CAST(targetasdecimal)* 100 asdecimal(3,0))[/COLOR]
[COLOR=black]ELSE 0[/COLOR]
[COLOR=#0000ff][COLOR=black]END[/COLOR]
[/COLOR]

Code: 
[/COLOR][COLOR=black]Error,[/COLOR]
[COLOR=black]Msg 8134, Level 16, State 1, Line 1[/COLOR]
[COLOR=black]Divide by zero error encountered.[/COLOR]
[COLOR=black]The statement has been terminated.[/COLOR]
[COLOR=black]
 
T

tehNellie

Registered User.
Local time
Today, 09:01
Joined
Apr 3, 2007
Messages
751
Eep, test on the wrong field for 0 replace GoneLive with Target on your test for > 0
 
T

tinyevil777

Registered User.
Local time
Today, 09:01
Joined
Dec 10, 2010
Messages
137
tehNellie said:
Eep, test on the wrong field for 0 replace GoneLive with Target on your test for > 0
Click to expand...

Now we're getting there! Thanks for your help thus far!

My only SLIGHT issue, is rounding (don't scream). A conversion percentage on record should be 5.88, however the new SQL update script has set it to 6.

Is there any modification we can make to the script that would accomodate for this?
 
T

tehNellie

Registered User.
Local time
Today, 09:01
Joined
Apr 3, 2007
Messages
751
AAAAAAAAAAAaaaaAaaaaaaaAAAAAgh! :D

yes,

That bit about decimal(3,0) setting to a maximum of 3 digits with 0 to the left of the decimal place?

try decimal(5,2) Maximium of 5 digits in total rounded to 2 decimal places. If you've got people going over 999.99% of their target you might need to tweak that a bit.
 
T

tinyevil777

Registered User.
Local time
Today, 09:01
Joined
Dec 10, 2010
Messages
137
tehNellie said:
AAAAAAAAAAAaaaaAaaaaaaaAAAAAgh! :D

yes,

That bit about decimal(3,0) setting to a maximum of 3 digits with 0 to the left of the decimal place?

try decimal(5,2) Maximium of 5 digits in total rounded to 2 decimal places. If you've got people going over 999.99% of their target you might need to tweak that a bit.
Click to expand...

Yes, yes and YES!

Happy days, thanks alot for your help! Finally got there eh?

The final working SQL Update query...

Code: 
UPDATE REPConversion
[COLOR=black]SET Conversion =CASEWHEN (GoneLive > 0)THENCAST(CAST(GoneLive asdecimal)/CAST(targetasdecimal)* 100 asdecimal(3,0))[/COLOR]
[COLOR=black]ELSE 0[/COLOR]
[COLOR=#0000ff][COLOR=black]END[/COLOR][/COLOR]

And on your comment of going over 999.99%... knowing our team, that won't be an issue ;)
 
T

tehNellie

Registered User.
Local time
Today, 09:01
Joined
Apr 3, 2007
Messages
751
Is now a bad time to mention that it's not really good practice to store a calculated value like that? If gonelive or target are updated before that query runs again then conversion isn't reflecting the values of those fields.

It would be much better to put that calculation in your output queries that use that table instead, that way you know that whenever you query the table Conversion is an accurate reflection of what's in those two columns now.

And of course you're not taking up diskspace storing a column of data that you can easily workout whenever you query the table in question.

(there is an element of context to be taken into consideration of course so feel free to tell me to mind my own beeswax)
 
T

tinyevil777

Registered User.
Local time
Today, 09:01
Joined
Dec 10, 2010
Messages
137
tehNellie said:
Is now a bad time to mention that it's not really good practice to store a calculated value like that? If gonelive or target are updated before that query runs again then conversion isn't reflecting the values of those fields.

It would be much better to put that calculation in your output queries that use that table instead, that way you know that whenever you query the table Conversion is an accurate reflection of what's in those two columns now.

And of course you're not taking up diskspace storing a column of data that you can easily workout whenever you query the table in question.

(there is an element of context to be taken into consideration of course so feel free to tell me to mind my own beeswax)
Click to expand...

Of course not, i appreciate all feedback.

Usually, yes, i'd not store calculated values in a table. However with this example, i actually have a temporary table which is created every night using a store procedure. Other SP's then manipulate this data, and the update script is part of the final SP. I could obviously still just calculate this field on the final output, however i may aswell just use update.

Phew... It's good to have a big project finished! I'm sure i'll be back once the goalposts change :)
 
You must log in or register to reply here.

Similar threads

D
Solved How to sort values (the highest first)? If duplicates values found, the sorting is based on the saved table for those values
Replies
13
Views
298
jdraw
jdraw
C
Solved Filling form fields from recordset
2
Replies
23
Views
658
ClaraBarton
C
H
TextBox preventing a set of records to update
Replies
6
Views
746
Shimon
S
oxicottin
Solved Calculate correct shift worked
Replies
9
Views
600
oxicottin
oxicottin
A
VBE custom code colors (syntax highlighting)
Replies
7
Views
813
MsAccessNL
MsAccessNL

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom