How to obtain the actual running balance within this query

Question

Following on from the question I originally asked yesterday (here), I was able to construct the following sql query that producded a running list of invoices and payments.

SELECT 
    'Invoice' AS TransactionType, 
    i.InvoiceNumber AS Description, 
    i.InvoiceDate AS TransactionDate, 
    CAST(ROUND(i.OutstandingBalance, 2) AS DECIMAL(12, 2)) AS TransactionAmount  
FROM 
    Invoices i
WHERE 
    i.CustomerId = 12 
    AND i.InvoiceDate BETWEEN '20150601' AND '20160229' 
    AND i.OutstandingBalance > 0.02

UNION

SELECT 
    'Payment' AS TransactionType, 
    ip.InvoicePaymentId AS Description, 
    ip.InvoicePaymentDate AS TransactionDate,
    - ip.Amount AS TransactionAmount  
FROM 
    InvoicePayments ip
WHERE 
    ip.CustomerId = 12 
    AND ip.InvoicePaymentDate BETWEEN '20150601' AND '20160229'
ORDER BY 
    TransactionDate

What I would now like to do is produce one extra column that is in effect the running balance on the account. I figured that if I started with a variable it should then be possible to add (or subtract from it to give me what I wanted). To that end I tried the following;

DECLARE @OutstandingBalance MONEY = 0

SELECT 
    'Invoice' AS TransactionType, i.InvoiceNumber AS Description, 
    i.InvoiceDate AS TransactionDate, 
    CAST(ROUND(i.OutstandingBalance, 2) AS DECIMAL(12, 2)) AS TransactionAmount,
    @OutstandingBalance + CAST(ROUND(i.OutstandingBalance, 2) AS DECIMAL(12, 2)) AS Balance 
FROM 
    Invoices i
WHERE 
    i.CustomerId = 12 
    AND i.InvoiceDate  BETWEEN '20150601' AND '20160229' 
    AND i.OutstandingBalance > 0.02

Which produced the results below.

However trying to modify the query by making it @OutstandingBalance += like so;

DECLARE @OutstandingBalance MONEY = 0

SELECT
    'Invoice' AS TransactionType, i.InvoiceNumber AS Description, 
    i.InvoiceDate AS TransactionDate, 
    CAST(ROUND(i.OutstandingBalance, 2) AS DECIMAL(12, 2)) AS TransactionAmount,
    @OutstandingBalance += CAST(ROUND(i.OutstandingBalance, 2)AS DECIMAL(12,2)) AS Balance 
FROM 
    Invoices i
WHERE 
    i.CustomerId = 12 
    AND i.InvoiceDate  BETWEEN '20150601' AND '20160229' 
    AND i.OutstandingBalance > 0.02

Throws an error telling me that the syntax is incorrect near the Keyword AS (which I presume refers to AS Balance. I suspect that I should probably be 'setting' the value of @OutstandingBalance but adding a set statement within the select also throws errors.

Is it possible to create a running balance in this sort of query and if so how does one accommodate setting the @OutstandingBalance to achieve it?

In response to the answer below this is the result set I get:

EDIT Revised query to accommodate both invoices and payments:

   SELECT 'Invoice' AS TransactionType, 
       i.InvoiceNumber AS Description, 
       i.InvoiceDate AS TransactionDate, 
       CAST(ROUND(i.OutstandingBalance,2)AS DECIMAL(12,2)) AS TransactionAmount , 
       SUM(CAST(ROUND(i.OutstandingBalance,2)AS DECIMAL(12,2))) OVER(ORDER BY i.InvoiceDate, i.InvoiceNumber) AS Balance 
FROM Invoices i
WHERE i.CustomerId = 12 
AND i.InvoiceDate  BETWEEN '20150601' AND '20160229' 
AND i.OutstandingBalance > 0.02

UNION

  SELECT 
    'Payment' AS TransactionType, 
    ip.InvoicePaymentId AS Description, 
    ip.InvoicePaymentDate AS TransactionDate,
    - ip.Amount AS TransactionAmount,
    SUM(CAST(ROUND(-ip.Amount,2) AS DECIMAL(12,2))) OVER(ORDER BY ip.InvoicePaymentDate,ip.InvoicePaymentId) AS Balance  

    FROM InvoicePayments ip
  WHERE ip.CustomerId = 12 
  AND ip.InvoicePaymentDate  BETWEEN '20150601' AND '20160229'



ORDER BY TransactionDate, Description

Which produces the following:

Zohar Peled · Accepted Answer · 2016-03-02T08:55:11.540

2

You can use SUM with an OVER clause like this:

SELECT 'Invoice' AS TransactionType, 
       i.InvoiceNumber AS Description, 
       i.InvoiceDate AS TransactionDate, 
       CAST(ROUND(i.OutstandingBalance,2)AS DECIMAL(12,2)) AS TransactionAmount , 
       SUM(CAST(ROUND(i.OutstandingBalance,2)AS DECIMAL(12,2))) OVER(ORDER BY i.InvoiceDate, i.InvoiceNumber) AS Balance 
FROM Invoices i
WHERE i.CustomerId = 12 
AND i.InvoiceDate  BETWEEN '20150601' AND '20160229' 
AND i.OutstandingBalance > 0.02
ORDER BY TransactionDate, Description

You can also use a cte to save one cast:

;WITH cte AS
(
SELECT 'Invoice' AS TransactionType, 
       i.InvoiceNumber AS Description, 
       i.InvoiceDate AS TransactionDate, 
       CAST(ROUND(i.OutstandingBalance,2)AS DECIMAL(12,2)) AS TransactionAmount
FROM Invoices i
WHERE i.CustomerId = 12 
AND i.InvoiceDate  BETWEEN '20150601' AND '20160229' 
AND i.OutstandingBalance > 0.02
)

SELECT TransactionType, 
       Description,
       TransactionDate,
       TransactionAmount,
       SUM(TransactionAmount) OVER(ORDER BY TransactionDate, Description) AS Balance 
FROM cte
ORDER BY TransactionDate, Description

edited Mar 02 '16 at 08:55

answered Mar 02 '16 at 08:28

Zohar Peled

79,642
10
69
121

Zohar, thank you for the answer, I hadn't come across over before so I have learnt something new, always nice!. I ran both of your suggestions and have appended a screen shot of the results to my question. Firstly the end result is all but correct (It's a penny out but that will be down to rounding somewhere) however why does the balance not increment on every line? Secondly can the same logic be applied to payments where the balance would show a reduction by the paid amount? – Dom Sinclair Mar 02 '16 at 08:47
I'm an idiot, it's summing at the end of the day as it were, makes perfect sense, just need to experiment with the payments side. – Dom Sinclair Mar 02 '16 at 08:51
I just noticed that the dates are not unique. I think you should add the `incodeNumber` to the order by clause both on the query and on the `over` clause. see my edited answer. – Zohar Peled Mar 02 '16 at 08:55
That is very neat, thank you. Can I apply the same logic to the second part of the original query at the top of my question, thus taking payments into account? – Dom Sinclair Mar 02 '16 at 09:01
I added my revision to you excellent query above, along with the result set. I'd tried with the latter query in your answer first but found it easier to produce an error free query with the syntax in your first answer. My guess is though that somehow I should be summing at the end as opposed to in both parts but can't seem to find the right syntax to get it to flow correctly. – Dom Sinclair Mar 02 '16 at 09:32
create a cte from the union query, then use the sum...over on the cte. this way your running balance would take both invoices and payments. – Zohar Peled Mar 02 '16 at 09:41

How to obtain the actual running balance within this query

1 Answers1