There is no T-SQL function for getting product of rows in a particular column. For example, say you had a table like this:
--/////////////////////////////select * from #tbltest order by mydate ascID MyDate Value1 1/1/99 102 1/1/99 203 1/1/99 304 2/1/99 405 2/1/99 50--/////////////////////////////If you want to get product of values for each date, here’s a simple way:
--/////////////////////////////select mydate, convert(varchar,exp(sum(log(value)))) as productfrom #tblTestgroup by mydate--/////////////////////////////Added to above solution, the following script is another way to check for zero and negative values using the sign function.
--/////////////////////////////select mydate,convert(varchar,case sum(case when sign(value) = -1 then 1else 0 end)%2when 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 productfrom #tblTestgroup by mydateorder by mydate asc--/////////////////////////////
