CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Prav
Prav

Reputation: 153

How to compute cumulative product in SQL Server 2008?

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

Upvotes: 7

Views: 6275

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 221275

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

Upvotes: 6

knot22
knot22

Reputation: 2768

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

Upvotes: 1

Kim
Kim

Reputation: 825

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

Upvotes: 1

Related Questions