I want to show the percentage for the Overshipment column.

My sample query

select (SPGI09_EARLY_OVER_T – (SPGI09_OVER_WK_EARLY_ADJUST_T) / (SPGI09_EARLY_OVER_T + SPGR99_LATE_CM_T + SPGR99_ON_TIME_Q))
from
CSPGI09_OVERSHIPMENT 

Table Name - CSPGI09_OVERSHIPMENT

My formula:

-------------------------------------------------------
SPGI09_EARLY_OVER_T – (SPGI09_OVER_WK_EARLY_ADJUST_T)
-------------------------------------------------------
SPGI09_EARLY_OVER_T + SPGR99_LATE_CM_T + SPGR99_ON_TIME_Q

and I want to show the result with %, for example, 66.57%

I'm using SQL SERVER 2008

1

2 Answers

Assuming all of these columns are int, then the first thing to sort out is converting one or more of them to a better data type - int division performs truncation, so anything less than 100% would give you a result of 0:

select (100.0 * (SPGI09_EARLY_OVER_T – SPGI09_OVER_WK_EARLY_ADJUST_T)) / (SPGI09_EARLY_OVER_T + SPGR99_LATE_CM_T + SPGR99_ON_TIME_Q)
from
CSPGI09_OVERSHIPMENT 

Here, I've mutiplied one of the numbers by 100.0 which will force the result of the calculation to be done with floats rather than ints. By choosing 100, I'm also getting it ready to be treated as a %.

I was also a little confused by your bracketing - I think I've got it correct - but you had brackets around single values, and then in other places you had a mix of operators (- and /) at the same level, and so were relying on the precedence rules to define which operator applied first.

4

Try something like this

select Cast((SPGI09_EARLY_OVER_T – (SPGI09_OVER_WK_EARLY_ADJUST_T) / (SPGI09_EARLY_OVER_T + SPGR99_LATE_CM_T + SPGR99_ON_TIME_Q)) as varchar(20) + '%' as percentageAmount
from CSPGI09_OVERSHIPMENT

I presume the value is a representation in percentage - if not convert it to a valid percentage total, then add the % sign and convert the column to varchar.

0

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy