Need Sql Queries for the scenario

Question

Trans Id  Date       Description       Credit        Debit 
1         12/05/2009 Amount Deposited   1000          1000
2         15/05/2009 Amount withdrawn   -500          500
3         20/05/2009 Deposited          2000          2500
4         25/05/2009 Amount withdrawn   -1000         1500
                             1500

Trans ID is the primary key

I need the output as follows

Trans Id  Date         Description       Amount
1         12/05/2009   Amount Deposited  1000
2         15/05/2009   Amount withdrawn  500
3         20/05/2009   Deposited         2000
4         25/05/2009   Amount withdrawn  1000
                       balance         1500

Need Sql query to generate the output

Answer 1

You could use a case statement to find the maximum, and abs to ensure a positive number:

select  [trans id]
,       date
,       description
,       abs(case when credit > debit then credit else debit end) as Amount
from    YourTable

Re-reading your question, I doubt this is the answer you're looking for. Please explain in a bit more detail.

Answer 2

I think this does what you want:

SELECT TransId, Date, Description, ABS(Credit) AS Amount
FROM transactions
UNION ALL
SELECT NULL, NULL, 'balance', SUM(Credit)
FROM transactions;

Result:

TransId  Date        Description       Amount
1        2009-05-12  Amount Deposited  1000            
2        2009-05-15  Amount Withdrawn  500             
3        2009-05-20  Deposited         2000            
4        2009-05-25  Amount Withdrawn  1000            
NULL     NULL        balance           1500