Daily Cumulative Customer Balance

[Former Member]

Hi Experts,

I want to Find Daily Cumulative Balance For Customer

kindly Help Me pls

Warm Regards,

Sandip Kokate

[Former Member]

Hi Sandip.....

If you run Party wise General Ledger report you get the Daily Cumulative Balance of Customer/Vendor. Just put the Date in from and To and run report.....

Financial Report> Accounting> General Ledger.....

Regards,

Rahul

[Former Member]

Hi

Actually I have gone throught this report but I want to add new fields in that Standard Report

Warm Regards,

Sandip Kokate

[Former Member]

Hi,

Do you just want to have two days like today and yesterday, or you want more days?

Thanks,

Gordon

[Former Member]

Hi Experts,

I just want it For today and Yesterday

kindly please give me solution

Warm Regards,

Sandip Kokate

[Former Member]

Try:

SELECT T0.CardCode,

T0.CardName,

T0.Balance 'Balance Today',

T0.Balance+SUM(IsNull(T1.Credit,0))-SUM(IsNull(T1.Debit,0)) 'Balance yesterday'

FROM OCRD T0

LEFT JOIN JDT1 T1 ON T1.ShortName=T0.CardCode AND DateDiff(DD,T1.RefDate,GetDate())=0

WHERE T0.CardType ='C'

Group BY T0.CardCode, T0.CardName, T0.Balance

[Former Member]

Hi

Actually I Have Tried on Your Query but it is not working Properly

I have added one more Entray on Today but still it will give same result for both the Reports

Warm Regards

Sandip Kokate

[Former Member]

HI Sandip ,

The query provided by Gordon is working Good .

Check the same again in your database .

Thanks

--

Ashish Ranjan

[Former Member]

If both columns display the same number, something is wrong in your database.

What is your B1 version/PL and SQL version?

[Former Member]

Hi

I am Using 2007 B and PL is 13

Thanking You

Sandip Kokate

[Former Member]

Try:

SELECT T0.CardCode,

T0.CardName,

T0.Balance 'Balance Today',

T0.Balance+SUM(IsNull(T1.Credit,0))-SUM(IsNull(T1.Debit,0)) 'Balance 10 day ago'

FROM OCRD T0

LEFT JOIN JDT1 T1 ON T1.ShortName=T0.CardCode AND DateDiff(DD,T1.RefDate,GetDate())=10

WHERE T0.CardType ='C'

Group BY T0.CardCode, T0.CardName, T0.Balance

To see if two columns are the same.

[Former Member]

Hi Sandipk

Try This

SELECT T0.[CardCode], T0.[CardName], T0.[ChecksBal], T0.[Balance], T0.[DNotesBal] as 'Del Balance', T0.[OrdersBal] as 'Sales Order Balance' FROM OCRD T0 WHERE T0.[CardType] ='C'

Thanks,

Srujal Patel

[Former Member]

Hi

Thanks For Response But I want Query which gives Transactiowise Cumulative Balance

Suppose Customers Todays Balance is 1000 and one more transaction of amount 100 happened on that Customer then his yesterdays balance should be 1100 and so on.

Warm Regards,

Sandip Kokate

[Former Member]

Hi Sandipk

In this Query All Balance Update per Transaction of Any Date

So, What do you want?

[Former Member]

Hi

Kindly go through

Financial ->Financial Reports ->Accounting ->General Ledger

I want to Create that kind of report because I want to add new fields in that Standard Report

Warm Regards,

Sandip Kokate