Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    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
    George
    Home | Blog

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    What's wrong with a stored procedure of TVF fed by a table variable?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Stored proc = one per table (i.e. whenever this action is required it needs to be customised)

    Table variable = not very scalable AFAIK?
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by gvee View Post
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm at a client's now, but I have a CTE idea that I think will be both scalable and easily templated. If you don't find an answer that suits you, I'll give it a whirl tomorrow.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •