[RESOLVED] SQL query help

[zuperman]

hi... i need help in this query...

SELECT Table1.transaction_account, Table1.transaction_id, Table1.transaction_date, Table1.transaction_amount_credit, Table1.transaction_amount_debit, Sum(Table2.transaction_amount_credit)-Sum(Table2.transaction_amount_debit) AS balance, payees.payee_name, categories.category_name, accounts.account_name
FROM transactions AS Table2, accounts INNER JOIN (categories INNER JOIN (payees INNER JOIN transactions AS Table1 ON payees.payee_id=Table1.transaction_payee) ON categories.category_id=Table1.transaction_category) ON accounts.account_id=Table1.transaction_account
WHERE (((Table1.transaction_account)=Table2.transaction_account) And ((Table2.transaction_date)<=Table1.transaction_date))
GROUP BY Table1.transaction_account, Table1.transaction_id, Table1.transaction_date, Table1.transaction_amount_credit, Table1.transaction_amount_debit, payees.payee_name, categories.category_name, accounts.account_name
HAVING (((Table1.transaction_account)=[?account_number]))
ORDER BY Table1.transaction_date, Table1.transaction_id;

an example of the result is this:

transaction_account	transaction_id	transaction_date	transaction_amount_credit	transaction_amount_debit	balance		payee_name		category_name	account_name
1			5		12-06-2006		250,00 €			0,00 €				250,00 €	Payee A			Category A	Account A
1			2		27-07-2006		0,00 €				50,00 €				220,00 €	Payee B			Category B	Account A
1			4		27-07-2006		20,00 €				0,00 €				220,00 €	Payee A			Category B	Account A
1			1		31-07-2006		100,00 €			0,00 €				320,00 €	Payee A			Category A	Account A

as you can see the balance is wrong when you have records in the same day...

thx in advance...

[zuperman]

here is the example database...
thx in advance

[zuperman]

ok...i done other way... created a trigger to execute a SP everytime transactions table is updated... with this SP i updated an sequencial order field ordereb dy date... this way i can have an unique identifier for the query...

this is the SP...in case anyone have the same issue:

CREATE PROCEDURE up_mM_trg_Transactions  AS
declare @intCustId int
declare @intCounter int
declare curTransaction cursor for

SELECT transaction_id FROM transactions ORDER BY transaction_date ASC
OPEN curTransaction

FETCH NEXT FROM curTransaction 
INTO @intCustId

SET @intCounter=1

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC up_mM_upd_transaction_order @intCounter, @intCustId
SET @intCounter = @intCounter + 1

FETCH NEXT FROM curTransaction 
INTO @intCustId

END

CLOSE curTransaction
DEALLOCATE curTransaction

GO