This should show $0 or not at all if removing zero balances
It's showing this with the code provided: 17-17=0 and not -17
0028874 A551989 2018-03-02 00:00:00.000 -17.00
For every invoice there may or may not be a payment or credit, etc ... but every invoice should show and show based on if there was other transactions against but once with the calculated result.
SELECT CustomerNo, InvoiceNo, TransactionDate,
SUM(TransactionAmt * CASE WHEN transactiontype IN ('C', 'P', 'X') THEN - 1 ELSE 1 END) AS Balance
FROM dbo.AR_TransactionPaymentHistory
GROUP BY InvoiceNo, CustomerNo, TransactionDate
HAVING (TransactionDate >= CONVERT(DATETIME, '2017-01-01 00:00:00', 102)) and
sum(transactionamt*case when transactiontype in ('C','P','X') then -1 else 1 end)<>0
And to add one more step, need to show TransactionDate of "I", the Invoice, 3/2/2018 was the payment date of the invoice. invoice date for this invoice was 1/8/2018
Ah, however that is needed and at the invoice. This is to be used to determine Aging and the date of the invoice is required with the invoice balance.
The invoice balance is determined by the above as they all "live" in this one table.
select customermo
,invoiceno
,min(case when transactiontype='I' then transactiondate else null end) as invoicedate
,sum(transactionamt*case when transactiontype in ('C', 'P', 'X') then - 1 else 1 end) as balance
from dbo.at_transactionpaymenthistory
group by invoiceno
,customerno
having transactiondate>=cast('2017-01-01' as datetime)
and sum(transactionamt*case when transactiontype in ('C','P','X') then -1 else 1 end)<>0
;
Msg 8121, Level 16, State 1, Line 8
Column 'dbo.AR_TransactionPaymentHistory.TransactionDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
select customermo
,invoiceno
,min(case when transactiontype='I' then transactiondate else null end) as invoicedate
,sum(transactionamt*case when transactiontype in ('C', 'P', 'X') then - 1 else 1 end) as balance
from dbo.at_transactionpaymenthistory
where transactiondate>=cast('2017-01-01' as datetime)
group by invoiceno
,customerno
having sum(transactionamt*case when transactiontype in ('C','P','X') then -1 else 1 end)<>0
;
Is there a way to not show, UNLESS it has an I, Invoice to the invoiceno?
CustomerNo
CustomerName
InvoiceNo
TransactionType
InvoiceType
TransactionDate
PaymentReference
CheckNo
PaymentDate
0028874
DEMAND MEDIA
7111023
P
IN
1/5/2017
ACH
1/5/2017
interestingly the invoice we were discussing above is no longer showing?
select customerno
,invoiceno
,min(case when transactiontype='I' then transactiondate else null end) as invoicedate
,sum(transactionamt*case when transactiontype in ('C', 'P', 'X') then - 1 else 1 end) as balance
from dbo.ar_transactionpaymenthistory
where InvoiceNo = 'A551989'
group by invoiceno
,customerno
having sum(transactionamt*case when transactiontype in ('C','P','X') then -1 else 1 end)<>0
;
select customermo
,invoiceno
,min(case when transactiontype='I' then transactiondate else null end) as invoicedate
,sum(transactionamt*case when transactiontype in ('C', 'P', 'X') then - 1 else 1 end) as balance
from dbo.at_transactionpaymenthistory
where transactiondate>=cast('2017-01-01' as datetime)
group by invoiceno
,customerno
having sum(transactionamt*case when transactiontype in ('C','P','X') then -1 else 1 end)<>0
and sum(case when transactiontype='I' then 1 else 0 end)>0
;
