CUSTOMER STMNT RUNNING BALANCE IS WRONG, ENTRIES SHOULD BE SORTED BY DATE

[Former Member]

Dear All,

I'm using the following query in order to make a customer statement with running balance.

SELECT T0.RefDate, T0.TransId, T0.Debit, T0.Credit, T0.ShortName, (select sum(Debit-Credit) from jdt1 where (RefDate<=T0.RefDate ) AND ShortName ='[%0]') [Running Total] FROM [dbo].[JDT1] T0 WHERE T0.ShortName ='[%0]' ORDER BY T0.RefDate, T0.TransId

The problem is that the balance is not calculated correctly. The entries should be sorted by posting date and then by transaction number, but the result i get has diffrent sorting. See below:

Posting Date Transaction Debit Credit Running Total

31.12.2007 17 557.285,30 0 557.285,30

02.01.2008 352 1.331,36 0 355.733,30

02.01.2008 353 5.196,82 0 355.733,30

02.01.2008 354 3.093,89 0 355.733,30

02.01.2008 355 4.152,92 0 355.733,30

02.01.2008 356 2.034,86 0 355.733,30

02.01.2008 1434 0 217.361,85 355.733,30

03.01.2008 357 1.966,78 0 376.891,26

03.01.2008 358 4.342,03 0 376.891,26

03.01.2008 359 4.947,19 0 376.891,26

Can you help me please?

Marina.

[Former Member · Accepted solution]

Hi,

I have tried you query and found out that Transaction Key is not sorted by Posting Date since some Transaction Key have earlier date, everytime you encode a Transaction, user sometimes change the posting date, that why is not sorted correctly but still it is a normal and correct transaction though.

Regards,

Clint

[Former Member · Accepted solution]

Hi Clint,

Thank you very much for your reply.

I know that it is normal behavior to enter back-dated transactions.

The problem is that on my statement i have to sort transactions by posting date,by transaction key, but in this case the running balance is wrong. I do not know which option to add in order to calculate the previous balance - it can not be a transaction key because of the sorting by posting date, and it can not be a date, because you can have many transactions posted on the same date.

Do you have any ideas?

Thank you.

Marina.

Edited by: Rui Pereira on May 1, 2009 10:15 AM