Need to add Account balance to this query

[Former Member]

Can someone advise how I can add the account balance to this query. Thanks Danielle SELECT distinct T0.[DocNum], T0.[DocDate], T0.[CardCode]"Customer Code",  T0.[CardName], t0.doctotal"Total Payment", T0.[PayNoDoc]"payment on account", isnull(t0.openbal, '0')"Not reconciled", t0.nodocsum"Total before partial reconcile", t0.nodocsum-t0.openbal as 'Reconcile Amount' FROM [dbo].[ORCT]  T0 left JOIN RCT2 T1 ON T0.Docnum = T1.DocNum WHERE T0.[PayNoDoc] = 'y'  and isnull(t0.openbal,'0') <> '0' and t0.doctype = 'c'

[kothandaraman_nagarajan · Accepted solution]

Hi,

Do you mean customer account balance?

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Yes

[kothandaraman_nagarajan · Accepted solution]

Try this:

SELECT distinct T0.[DocNum], T0.[DocDate], T0.[CardCode]"Customer Code",  T0.[CardName], t0.doctotal"Total Payment", T0.[PayNoDoc]"payment on account", isnull(t0.openbal, '0')"Not reconciled", t0.nodocsum"Total before partial reconcile", t0.nodocsum-t0.openbal as 'Reconcile Amount' ,T2.[Balance]

FROM [dbo].[ORCT]  T0 left JOIN RCT2 T1 ON T0.Docnum = T1.DocNum left join OCRD T2 on t2.cardcode = T0.cardcode WHERE T0.[PayNoDoc] = 'y'  and isnull(t0.openbal,'0') <> '0' and t0.doctype = 'c'