CODEWITHSUNDEEP
sqlsql-server
melegant
melegant

Reputation: 1709

Calculate Running Increase of Number in SQL Server

I have an interesting problem to solve of increasing a number by multiple percentages.

Let's say I have a widget and I want to increase the cost of this widget by percentages in a table.

WidgetPercent

WidgetID | Percent
---------+--------
1          .10
1          .06

Widget

WidgetID | Cost
---------+-------
1           100

I want to increase the cost of the widget by the first percent, then that total with the next percent and output a single value.

Example

100 * 1.10 = 110
110 * 1.06 = 116.60

Output should be 116.60. Results would be by WidgetID

I think I should use OVER (https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15) with SUM but not quite sure in this context.

Note that the environment is SQL Server 2012 forward.

Upvotes: 1

Views: 145

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can use arithmetic like this:

select w.widgetid, w.cost * wp.factor
from (select widgetid, exp(sum(log(1 + percent))) as factor
      from widgetpercent wp
      group by widgetid
     ) wp join
     widget w
     on wp.widgetid = w.widgetid;

Here is a db<>fiddle.

Upvotes: 1

Related Questions