0

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.

3

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 ;
  • but from your answer this means that I get the sum of TotalAmount then use the function to calculate the tax instead of directly getting the calculated tax amount right ? – JamesYTL Oct 17 '16 at 10:27
  • 1
    No, it calculates the ReturntaxAmount of every row, then adds them together over (whatever the OVER clause says, partition by, order by). – ypercubeᵀᴹ Oct 17 '16 at 10:38
  • umm ok let me try and I will tell the result because the actual query is actually way more complicated than in question – JamesYTL Oct 17 '16 at 10:44
  • Thanks as it really did solved my issue! Changing the position of SUM and tweaking around my code lead to the result I wanted. – JamesYTL Oct 17 '16 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.