Getting Running Total from user defined function

Question

I have the following query.

Select x1.TotalAmount, [ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount, 
       x2.DocumentType 
FROM table1 X1 
  INNER JOIN table2 x2 on t1.id = t2.t1id

Now how do I get the running total of self-declared TaxAmount? I wish to do something like this

Select x1.TotalAmount, 
       SUM([ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount) OVER (PARTITION BY x3.InvId
          ORDER BY x3.createddate
          ROWS BETWEEN UNBOUNDED PRECEDING
          AND 1 PRECEDING) AS TaxAmount, 
       x2.DocumentType 
FROM table1 X1 
  INNER JOIN table2 x2 on t1.id = t2.t1id

[ReturnTaxAmount] is a function that will return calculated decimal value.

This is the method I tried but failed which suspected is because of the table hasn't really being generated yet.

Is it possible to do so or must I create a new declared/temp table to store the table and select the table again to get the running total of it.

Answer 1

You got the syntax slightly wrong. It should be:

SELECT 
    x1.TotalAmount, 
    SUM([ReturnTaxAmount](x1.TotalAmount,@TaxRate))    -- no AS TaxAmount here
        OVER (PARTITION BY x3.InvId
              ORDER BY x3.createddate
              ROWS BETWEEN UNBOUNDED PRECEDING
              AND 1 PRECEDING) AS TaxAmount,           -- it's ok here
    x2.DocumentType 
FROM table1 x1 
  INNER JOIN table2 x2 ON t1.id = t2.t1id 
--- the rest of the code, that joins x3, etc
  ;

The alias should be only once, after the whole column expression, which includes the SUM(...) OVER (...)

You could use a derived table or a CTE as your idea (no need for temporary table). It would be equivalent:

SELECT 
    t.TotalAmount, 
    SUM(t.TaxAmount)                    -- here we use the alias, defined in 
        OVER (PARTITION BY t.InvId      -- the derived table, below
              ORDER BY t.createddate
              ROWS BETWEEN UNBOUNDED PRECEDING
              AND 1 PRECEDING) AS TaxAmount,  
    t.DocumentType 
FROM 
    ( SELECT 
          x1.TotalAmount, 
          [ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount, 
          x2.DocumentType,
          x3.InvId,
          x3.createddate
      FROM table1 x1 
        INNER JOIN table2 x2 ON t1.id = t2.t1id
      --- the rest of the code, that joins x3, etc
    ) AS t ;