Hi Sergei
see below, i am comparing the sum result with vendor to ledger reconciliation report
not sure if this is the right logic , but i would like the query to sum all the voucher numbers from vend trans that exist in the GAE and the filtered main account and posting type= vendor balance
Thanks
Amal
SELECT MA.Name AS [Main account name], MA.Value AS [Main Account], GAE.AccountingCurrencyAmount AS [Ledger Balance], GAE.ReportingCurrencyAmount AS [Ledger Balance],
std.VendorTransactions.AmountMST AS Expr1, std.VendorTransactions.ReportingCurrencyAmount AS Expr2, GE.SubledgerVoucherDataAreaId
FROM std.VendorTransactions INNER JOIN
std.Generaljournalentry AS GE INNER JOIN
std.GeneralJournalaccountentry AS GAE INNER JOIN
dbo.MainAccount AS MA ON GAE.MainAccountId = MA.Id ON GE.RecId = GAE.GeneralJournalEntry ON std.VendorTransactions.Voucher = GE.SubledgerVoucher AND
std.VendorTransactions.TransDate = GE.AccountingDate
WHERE (GE.AccountingDate BETWEEN CONVERT(DATETIME, '2020-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2020-01-31 00:00:00', 102)) AND (GAE.Postingtype = 41) AND (MA.Value = N'921100')
