Skip to Content
0

SAP Business one Account Balance+Postdated Check Query

Apr 24 at 03:36 AM

28

avatar image

Good Day,

Idk if this is possible to join those tables but i want to achieve is

Cardcode,Account Balance,PDC

please see below my query codes

Select T0.CardCode,T0.CardName,(SUM(T1.Debit)- SUM(T1.Credit)) AS Balance From OCRD T0 Inner Join JDT1 T1 on T0.CardCode=T1.Shortname Right join Ochh T2 on T0.CardCode=T2.CardCode Where T0.Cardtype='c' Group By T0.CardCode,T0.CardName Having (SUM(T1.Debit)- SUM(T1.Credit))!=0

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Romel Catalogo Apr 25 at 12:56 AM
0

Thanks for the Answer,

I already solved my problem in this query,

Declare @CustCode varchar(8000)

set @Custcode=(select S1.CardCode from OCRD S1 where S1.CardCode='[%0]')

Select T0.cardcode,T0.Cardname, (select sum(J0.debit-J0.credit)

from JDT1 J0 where j0.shortname=T0.Cardcode) as balance,

(SELECT sum(T1.[CheckSum]) FROM OCHH T1 WHERE T1.Deposited = 'N' and T1.Canceled ='N' and T1.[CashCheck] IN ('Chk Clr-Mnl','Chk Clr-Naga','Chk Clr-Cal','Chk Clr-Lucena') and T1.CardCode = T0.CardCode) as PDC,

(SELECT sum(T2.[CheckSum]) FROM OCHH T2 INNER JOIN ODPS T3 ON T2.DpstAbs = T3.DeposId WHERE T2.cardcode=t0.cardcode and T3.[DeposDate]>=getdate()) as 'Uncleared Check'

from OCRD T0

Where T0.Cardtype='C' and T0.Cardcode=@Custcode

Group By T0.CardCode,T0.CardNAme

Share
10 |10000 characters needed characters left characters exceeded
Mark Hurenkamp Apr 24 at 10:55 AM
0

Dear Romel,

At the moment I only have access to a HANA database, but it should work similarly for you:

SELECT T0."CardCode", T0."CardName", (SUM(T1."Debit") - SUM(T1."Credit")) AS "Balance"
FROM "OCRD" T0 INNER JOIN "JDT1" T1 ON T0."CardCode" = T1."ShortName" 
RIGHT OUTER JOIN "OCHH" T2 ON T0."CardCode" = T2."CardCode" 
WHERE T0."CardType" = 'C'GROUP BY T0."CardCode", T0."CardName" 
HAVING (SUM(T1."Debit") - SUM(T1."Credit")) <> 0

Could you try if this one works for you?

Kind regards,

Mark

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Apr 24 at 01:10 PM
0

Hi,

Try this query,

Select T0.CardCode,T0.CardName, SUM(T1.Debit- T1.Credit) AS Balance ,(SELECT SUM(Ta.CheckSum) FROM OCHH Ta where Ta.[Deposited] = 'N' and Ta.cardcode =T0.Cardcode) as PDC

From OCRD T0 Inner Join JDT1 T1 on T0.CardCode=T1.Shortname Where T0.Cardtype='c'

Group By T0.CardCode,T0.CardName

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

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded