--------------------------
Date ID Type Debit Credit Balance
1-10-06 DN-1111 DN 86.00 0.00 86.00
4-10-06 CN-8888 PAY 0.00 56.00 30.00
1-11-06 DN-2222 DN 120.00 0.00 150.00
1-12-06 DN-3333 DN 15.00 0.00 165.00
5-12-06 CN-9999 PAY 0.00 165.00 0.00
--------------------------
The above query result is wat i trying to get..
but my current sql statement can't calculate the [balance]
the condition to calculate as below
if 4-10-06's type = PAY, Then
4-10-06's [balance] = 1-10-06's [balance] MINUS 4-10-06's [credit]
--------------------------
if 4-10-06's type = DN, Then
4-10-06's [balance] = 1-10-06's [balance] ADD 4-10-06's [debit]
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Try:
SELECT i.date, i.ID, i.Type, SUM(i.debit) AS debit, SUB(i.credit) AS credit FROM
(
SELECT dn.date, dn.dn_id AS ID, dn.Type, dn.amount AS Debit, 0.00 AS Credit FROM debit_note_table dn
UNION ALL
SELECT cn.date, cn.cn_id AS ID, cn.Type, 0.00 AS Debit, cn.amount AS Credit FROM credit_note_table cn
) i
GROUP BY i.date, i.ID, i.Type
ORDER BY date ASC
Sorry, forgot to add the balance:
SELECT i.date, i.ID, i.Type, SUM(i.debit) AS debit, SUB(i.credit) AS credit, SUM(i.credit - i.debit) AS balance FROM
(
SELECT dn.date, dn.dn_id AS ID, dn.Type, dn.amount AS Debit, 0.00 AS Credit FROM debit_note_table dn
UNION ALL
SELECT cn.date, cn.cn_id AS ID, cn.Type, 0.00 AS Debit, cn.amount AS Credit FROM credit_note_table cn
) i
GROUP BY i.date, i.ID, i.Type
ORDER BY date ASC
todd-
I think he needs a running balance from all the transactions. I don't think your code will deliver that.
Go to the shell and do something like this:
SET @bal=0;
SELECT i.date, i.ID, i.Type, i.debit, i.credit, (@bal:=@bal-i.debit+i.cred
(
SELECT dn.date, dn.dn_id AS ID, dn.Type, dn.amount AS Debit, 0.00 AS Credit FROM debit_note_table dn
UNION ALL
SELECT cn.date, cn.cn_id AS ID, cn.Type, 0.00 AS Debit, cn.amount AS Credit FROM credit_note_table cn
) i ORDER BY date ASC
mankowitz:
Yeah, I didn't understand the problem sufficiently, it seems. I'm not sure that using a variable works here, since that is evaluated before the ORDER BY clause. Maybe something like:
SELECT i.date, i.ID, i.Type, SUM(i.debit) AS debit, SUB(i.credit) AS credit, (SELECT SUM(i2.credit - i2.balance) FROM
SELECT dn2.amount AS Debit, 0.00 AS Credit FROM debit_note_table dn2 WHERE dn2.date < i.date
UNION ALL
SELECT 0.00 AS Debit, cn2.amount AS Credit FROM credit_note_table cn2 WHERE cn2.date < i.date
) i2
)AS balance FROM
(
SELECT dn.date, dn.dn_id AS ID, dn.Type, dn.amount AS Debit, 0.00 AS Credit FROM debit_note_table dn
UNION ALL
SELECT cn.date, cn.cn_id AS ID, cn.Type, 0.00 AS Debit, cn.amount AS Credit FROM credit_note_table cn
) i
GROUP BY i.date, i.ID, i.Type
ORDER BY date ASC;
Lots of ugliness here with correlated subqueries.
Business Accounts
Answer for Membership
by: kengkitPosted on 2007-01-24 at 02:26:43ID: 18384270
Date ID Type Debit Credit
1-10-06 DN-1111 DN 86.00 0.00
4-10-06 CN-8888 PAY 0.00 56.00
1-11-06 DN-2222 DN 120.00 0.00
1-12-06 DN-3333 DN 15.00 0.00
5-12-06 CN-9999 PAY 0.00 165.00
i can get the above query result by using the following sql..
--------------------------
SELECT i.date, i.ID, i.Type, i.debit, i.credit FROM
(
SELECT dn.date, dn.dn_id AS ID, dn.Type, dn.amount AS Debit, 0.00 AS Credit FROM debit_note_table dn
UNION ALL
SELECT cn.date, cn.cn_id AS ID, cn.Type, 0.00 AS Debit, cn.amount AS Credit FROM credit_note_table cn
) i ORDER BY date ASC