Account Receivable Balance As of a particular date

I am trying run this query to extract the exact GL Balance as of a particular date but it is giving the net change

Example

Customer A

04/01/2018 Opening Balance      $100

04/10/2018 Invoice                      $  50

04/25/2018 Payment                   $  25

04/30/2018 Closing Balance         $ 125 

Customer B

04/01/2018 Opening Balance      $150

04/10/2018 Invoice                      $  150

04/25/2018 Payment                   $  125

04/30/2018 Closing Balance         $  25 

But if I run the query AR GL I get $ 50. The query for checking GL Balance is as under:

select SUM(DEBITAMT)-SUM(crdtamnt) from GL20000 where ACTINDX in
(select ACTINDX from GL00105 where ACTNUMST = '1100-000')
and YEAR(trxdate) = '2018' and MONTH(trxdate) = 4
and ORDOCNUM <> '' 

and similarly the AR Detailed query provides me $50

SELECT
--Open Items
GL.JRNENTRY,
----Key
(select cmpnynam from dynamics..sy01500 where interid = (select db_name())) [Company ID],
----Business Partner
AR.[Document Abbreviation][Type],
AR.[Document Type][Document Type],
AR.[Customer Number][Customer ID],
GL.ORMSTRNM [Customer Name],
AR.[Tax Registration Number] [Tax Registration Number],
----Document Details
AR.[Document Date][Document Date],
AR.[Document Number][Document Number],
AR.[Document Description][Transaction Description],
AR.[Current Trx Amount][Current Trx Amount],
----Transaction Currency
AR.[Currency ID][Currency ID],
AR.[Sales Amount][Sales Amount],
AR.[Discount Taken Amount][Discount Amount],
AR.[Document Amount][Document Amount],
----Company Currency
(select FUNLCURR from MC40000) [Company Currency],
AR.[Document Amount][Document Amount],
----Payment Terms
AR.[Due Date][Due Date],
AR.[Payment Terms ID][Payment Terms ID],
----Bank Details
----Data for Import of Goods
----Legally Required Invoice Data
----Additional Assignment
--Tax Data
--Withholding Tax Data
ORCTRNUM [Document Number],
CRDTAMNT [Credit Amount],
DEBITAMT [debit Amount],
ORCRDAMT [Orginating Credit Amount],
ORDBTAMT [Orginating Debit Amount]
FROM
GL20000 GL LEFT OUTER JOIN
ReceivablesTransactions AR ON
GL.ORDOCNUM = AR.[Document Number]
where GL.ACTINDX in
(select ACTINDX from GL00105 where ACTNUMST = '1100-000')
and YEAR(trxdate) = '2018'
and MONTH(trxdate) = '4'
and ORDOCNUM <> ''

Help will be appreciated!

Thanks,

Related
Recommended