Thread: How to make this repeatable?

1. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445

Unanswered: How to make this repeatable?

I'm a bit embarrassed to be asking the question but I think a lack of sleep and caffeine is being particularly unkind to me today.

So I have written a bit of code to calculate a running product:
Code:
```DECLARE @t table (plus_n int, i decimal(5,2));

INSERT INTO @t (plus_n, i)
VALUES (1, 1.40)
, (2, 1)
, (3, -1.02)
, (4, 0)
, (5, -3.1)
;

SELECT t.plus_n
, t.i
, x.result As running_product
FROM   @t As t
CROSS
APPLY (
SELECT base_value * negative_compensator As result
FROM   (
SELECT CASE
WHEN Min(Abs(i)) = 0 THEN -- If any value is zero then the result is zero.
0
ELSE -- Calculate the product of the absolute values
Exp(Sum(Log(Abs(NullIf(i, 0))))) -- Credit: Peter Larsson http://weblogs.sqlteam.com/peterl/archive/2008/11/19/How-to-get-the-productsum-from-a-table.aspx
END As base_value
, CASE -- Work out whether we have an even or odd number of negative values to compensate for the absolution
WHEN Sum(CASE WHEN i < 0 THEN 1 ELSE 0 END) % 2 = 1 THEN
-1
ELSE
+1
END As negative_compensator
FROM   @t
WHERE  plus_n <= t.plus_n
) As crunchy
) AS x
ORDER
BY plus_n
;```
However I feel the need to encapsulate / make this code repeatable so that others can use it easily... but I can't think of a way to do it!

Any ideas?

Cheers

2. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
What's wrong with a stored procedure of TVF fed by a table variable?

3. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Stored proc = one per table (i.e. whenever this action is required it needs to be customised)

Table variable = not very scalable AFAIK?

4. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
Originally Posted by gvee
Stored proc = one per table (i.e. whenever this action is required it needs to be customised)

Table variable = not very scalable AFAIK?
I don't understand the problem. In the real world scenario. Is @t representing a number of different tables? All with the same structure?

If performance is what you want, often string manipulation and complicated mathematics are best left to the application code and not the database code.

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579