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
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 float
s rather than int
s. 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With