cancel
Showing results for 
Search instead for 
Did you mean: 

need bp account balance in the query

Former Member
0 Kudos

Hi Experts

i need bp account balance in this Query

------------------------------

Select *

from

(

Select ROW_NUMBER() over ( partition by Cardcode Order by Docentry desc) as Ord,

OPCH .DocNum ,OPCH .DocDate,OPCH .CardCode ,OPCH .CardName ,OPCH .DocTotal

from OPCH

) D

Where D.Ord = 1 and d.CardCode like '%SP%' ORDER BY D.DocDate

Regards

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member185296
Active Participant
0 Kudos

Hi,

SELECT T1.[ShortName], T4.[CardName], T1.Account, T2.[AcctName],  Isnull((SELECT SUM(T3.Debit - T3.Credit) FROM OJDT T2

INNER JOIN JDT1 T3 ON T2.TransId = T3.TransId

INNER JOIN OCRD T11 ON T3.[ShortName] = T11.[CardCode]

WHERE T2.RefDate < '[%0]' AND T3.ShortName LIKE T1.ShortName

GROUP BY T3.Account),0) 'Opening balance',

     SUM(T1.Debit) 'Debit', SUM(T1.Credit) 'Credit',

     SUM(T1.Debit - T1.Credit)  AS 'Balance'

FROM OJDT T0

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

INNER JOIN OACT T2 ON T1.Account = T2.AcctCode

INNER JOIN OCRD T4 ON T1.[ShortName] = T4.[CardCode]

WHERE T0.RefDate >= '[%0]' AND T0.RefDate <= '[%1]'

GROUP BY

T1.[ShortName], T4.[CardName], T1.Account, T2.[AcctName]

Having SUM(T1.Debit - T1.Credit) != 0

Regards,

Vinoth