Hi Expert,
i want a query that will fuse invoice made,receipt paid on that invoice and receipt paid on account and then reconciled .
A join from these three areas to produce the below columns will be appreciated;
Invoice Number, Receipt Number,CardCode,CardName, Invoice Amountt, Paid amount, Balance, Reconciled Amount,.
Below is a query i wrote but the result has a lot of duplicates. If anyone can work on it to remove duplicates .
SELECT T0.CardCode, T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[DocTotal], T2.[DocNum] 'Incoming Pay No.', T2.[DocDate] 'Incoming Pay Date', T0.[PaidToDate] 'Total Paid', T0.DocTotal-T0.[PaidToDate] 'Balance Amt' ,T3.ReconNum AS 'Reconciliation Number', t3.ReconDate as 'Reconciliation Date' FROM OINV T0 INNER JOIN RCT2 T1 ON T0.DocEntry=T1.baseabs INNER JOIN ORCT T2 ON T1.[DocNum] = T2.[DocEntry] left JOIN OITR T3 ON T2.DocNum=T3.InitObjAbs --left JOIN ITR1 T4 ON T3.ReconNum=T4.ReconNum ORDER BY T2.DocNum
Regards
Justice