SELECT
case when coalesce(prc,0)=0 then NULL
else ((cost/prc)*100) end
as pct
from ps_tkt_hist_lin

quote:

---

IF you give something away for free....then isn't the pct loss equal to 100...ie the lot!!

---

quote:

---

Originally posted by steamngn
The returned value for profit percentage is .250000
How can I return the value as 25.0?

---

select case
when coalesce(prc,0)=0 and coalesce(reg_prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce(prc,0)=0 and coalesce(reg_prc,0)<>0 and coalesce(cost,0)<>0
then cast(-((reg_prc-cost)/cost)as numeric)
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
then cast((((prc-cost)/cost)*100) as numeric)
end
as pct,
 case
when coalesce(prc,0)=0 and coalesce(reg_prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce(prc,0)=0 and coalesce(reg_prc,0)<>0 and coalesce(cost,0)<>0
then cast(-((reg_prc-cost))as numeric)
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
then((prc-cost))
end
as profit_amt,
ps_tkt_hist_lin.cost,tkt_no,ps_tkt_hist_lin.item_no, im_item.categ_cod
from ps_tkt_hist_lin
join im_item
on ps_tkt_hist_lin.item_no=im_item.item_no
and im_item.item_no<>'NOTE'
and im_item.categ_cod<>'WALAB'
and im_item.categ_cod<>'RELAB'

then cast(-((reg_prc-cost)/cost)as numeric)

quote:

---

This is the current code. This line:

then cast(-((reg_prc-cost)/cost)as numeric)

will return -1.00000, but if I multiply it by 100 then I get the ACTUAL calculation, say 149 or something rather than 100, for the percentage. It is the negative amounts that aren't calculating right..

---

then cast(-((reg_prc-cost)/cost)*100 as numeric)

then cast(-((reg_prc-cost)/cost)as numeric)*100