7

I have below table with 2 columns, DATE & FACTOR. I would like to compute cumulative product, something like CUMFACTOR in SQL Server 2008.

Can someone please suggest me some alternative.enter image description here

Prav
  • 153
  • 2
  • 6

3 Answers3

5

Unfortunately, there's not PROD() aggregate or window function in SQL Server (or in most other SQL databases). But you can emulate it as such:

SELECT Date, Factor, exp(sum(log(Factor)) OVER (ORDER BY Date)) CumFactor
FROM MyTable
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Hello Lukas, thanks for your quick response. Currently I'm in SQL Server 2008, unfortunately LEAD and LAG functions are not available in this version. So any other alternatives ? – Prav Mar 01 '16 at 08:45
  • 1
    @Lukas Eder: Neither solution matches the values in the OP's CumFactor column. – knot22 Mar 03 '17 at 15:22
  • @knot22: Meh, thanks for the hint. The question was edited 2 days after my answer was accepted :( Will fix my answer – Lukas Eder Mar 03 '17 at 16:16
1

You can do it by:

SELECT A.ROW
    , A.DATE
    , A.RATE
    , A.RATE * B.RATE AS [CUM RATE]
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY DATE) as ROW, DATE, RATE
    FROM TABLE
) A
LEFT JOIN (
    SELECT ROW_NUMBER() OVER(ORDER BY DATE) as ROW, DATE, RATE
    FROM TABLE
) B
ON A.ROW + 1 = B.ROW
Kim
  • 771
  • 6
  • 23
1

To calculate the cumulative product, as displayed in the CumFactor column in the original post, the following code does the job:

--first, load the sample data to a temp table
select *
into #t
from 
(
  values
  ('2/3/2000', 10),
  ('2/4/2000', 20),
  ('2/5/2000', 30),
  ('2/6/2000', 40)
) d ([Date], [Rate]);

--next, calculate cumulative product
select *, CumFactor = cast(exp(sum(log([Rate])) over (order by [Date])) as int) from #t;

Here is the result:
enter image description here

knot22
  • 2,648
  • 5
  • 31
  • 51