Aggregate the product

Sample data

Simply define the data, a table with only years and interest.

-- テーブル定義
declare @利息マスタ table 
(
  年月 nvarchar(6)
 ,利息 decimal(4, 2)
)

-- サンプルデータ挿入
insert @利息マスタ
          select N'201501', 1.05
union all select N'201502', 1.03
union all select N'201503', 1.02
union all select N'201504', 1.10
union all select N'201505', 1.02
union all select N'201506', 1.06
union all select N'201507', 1.07
union all select N'201508', 1.04
union all select N'201509', 1.06
union all select N'201510', 1.07
union all select N'201511', 1.03
union all select N'201512', 1.02

-- 確認
select * from @利息マスタ

Summary of the product

The sum of the product can be calculated by the aggregate of "exponential function" and "logarithmic function". SQL looks like this:

-- 集計
select exp(sum(log([利息]))) from @利息マスタ
-- 結果：1.73968081491318

This can be achieved by the nature of exponential functions and logarithmic functions.

First, the log function summary is officially"log x + log y = log(x *y)". The formula of the exponential function,e^logx = x**, is"e^log(x*y) = x * y"when the formula determined by the sum of the logarithmic functions is applied. (x* y * ... will be the number of data)

Zero and negative responses

Because logarithmic functions cannot be less than or equal to 0, an error occurs if the aggregate disaggregated includes a number less than or equal to 0. To accommodate a number less than or equal to 0, follow SQL as follows:

-- 集計(ゼロ、負対応)
select
  case
    when min(abs([利息])) = 0 then 0
    else
       exp(sum(log(abs(nullif([利息], 0)))))
     * round(0.5 - count(nullif(sign(sign([利息]) + 0.5), 1)) % 2, 0)
  end as [利息合計]
from @利息マスタ
-- 結果：1.73968081491318