create table #tbltest (value numeric(3,1))
insert #tblTest values(-1.5)
insert #tblTest values(1.5)
insert #tblTest values(2.5)
insert #tblTest values(0)
select * from #tbltest

select case sum(case when sign(value) = -1 then 1 else 0 end)%2 when 1 then -1 else 1 end
        * exp(sum(log(abs(case when sign(value) <> 0 then value end))))
        * min(case when value = 0 then 0 else 1 end) as Prod1,
        case when sum(case when sign(value) = -1 then 1 end) > 0 then -1 else 1 end
        * exp(sum(log(abs(case when sign(value) <> 0 then value end))))
        * min(case when value = 0 then 0 else 1 end) as Prod2
from #tblTest

delete #tbltest where value = 0
select case sum(case when sign(value) = -1 then 1 else 0 end)%2 when 1 then -1 else 1 end
        * exp(sum(log(abs(case when sign(value) <> 0 then value end))))
        * min(case when value = 0 then 0 else 1 end) as Prod1,
        case when sum(case when sign(value) = -1 then 1 end) > 0 then -1 else 1 end
        * exp(sum(log(abs(case when sign(value) <> 0 then value end))))
        * min(case when value = 0 then 0 else 1 end) as Prod2
from #tblTest

delete #tbltest where sign(value) = -1
select case sum(case when sign(value) = -1 then 1 else 0 end)%2 when 1 then -1 else 1 end
        * exp(sum(log(abs(case when sign(value) <> 0 then value end))))
        * min(case when value = 0 then 0 else 1 end) as Prod1,
        case when sum(case when sign(value) = -1 then 1 end) > 0 then -1 else 1 end
        * exp(sum(log(abs(case when sign(value) <> 0 then value end))))
        * min(case when value = 0 then 0 else 1 end) as Prod2
from #tblTest

This page was last updated on May 01, 2006 04:28 PM.