Okay - I've been asked to create a query where the returned result is an AR Balance.
The Receivables Transactions SQL view has everything I need. However, I need to add a running Total to my query.
Since some transactions are negative amounts (credits payments returns) the traditional sum over technique doesn't work.
Here's what I got:
select
CAST([Document Date] as date) Date,
[Customer Number],
[Customer Name],
[Document Number],
[Accounts Receivable Account Number],
[Document Type],
Case When [Document Type] = 'Sales / Invoices' then [Original Trx Amount]
When [Document Type] = 'Debit Memos' then [Original Trx Amount]
When [Document Type] = 'Finance Charges' then [Original Trx Amount]
When [Document Type] = 'Payments' then [Original Trx Amount]*-1
When [Document Type] = 'Returns' then [Original Trx Amount]*-1
When [Document Type] = 'Credit Memos' then [Original Trx Amount]*-1
else '0' end as TransactionAmnt,
CumulativeAR=SUM([Original Trx Amount]) OVER (ORDER BY [Document Date],[Document Number]),
[Void Status]
from ReceivablesTransactions where [Customer Number]='33855'
and [Void Status] <> ''
ORDER BY [Document Date] ASC
CumulativeAR running total is incorrect - I need to do a running total on my calculated 'TransactionAmnt' column - rather than [Original Trx Amount]
Any thoughts?