June 2, 2011 at 4:31 am
The data in the table is like this
Voucher No. Bill NO. Amount
-----------------------------
1283 543 300000
1283 543 -300000
1282 4545 7004
1282 4545 -7004
They want a Report like this
Voucher No. Bill NO. Debit Credit
--------------------------------------
1283 543 300000 0.00
1283 543 0.00 300000
Voucher Total 300000 300000
1282 4545 7004 0.00
1282 4545 0.00 7004
Voucher Total 7004 7004
Day Total 307004 307004
How to do this if im using Grouping based on Voucher No.
since the amount is from same column
it is adding 300000 and -300000
and giving 0.00 as total
how to resolve this
any help ?
June 2, 2011 at 6:34 am
Hi,
Assuming the table is called "Vouchers" and assuming I understand what you're after then this should do the trick:
SELECT
Voucher_No
, Bill_No
, CASE
WHEN Amount < 0
THEN
Amount
ELSE
0
END AS Debit
, CASE
WHEN Amount > 0
THEN
Amount
ELSE
0
END AS Credit
FROM
Vouchers
Then you can calculate your totals from there now that the column is split.
June 2, 2011 at 10:18 pm
Thank u very much it is working great !
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply