Skip to Content
0
Former Member
Feb 25, 2009 at 03:06 PM

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

11 Views

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.