SQL Working with two rows at the same time.

[mmezzeld]

My transaction table has tran code SSN, and an amount, followed by another Tran code for same SSN, and the Amount is -ve. I habe to create report that has SSN and amount (= psitive - negative) how ?

[Rina0]

To create a report that shows the SSN and the net amount (positive - negative) for each SSN, you can use SQL to perform the necessary calculations. Here's a sample SQL query that should achieve this:

sql
SELECT SSN, SUM(amount) AS net_amount
FROM (
    SELECT SSN, 
           CASE 
               WHEN tran_code = 'SSN' THEN amount
               ELSE -amount
           END AS amount
    FROM transaction_table
) AS t
GROUP BY SSN;
 

Explanation:
1. The inner subquery (aliased as `t`) uses a `CASE` statement to differentiate between positive and negative amounts based on the `tran_code`. If the `tran_code` is 'SSN', it keeps the amount as is, otherwise, it negates the amount.
2. The outer query then groups the results from the subquery by `SSN` and calculates the `SUM` of the `amount` for each `SSN`, giving you the net amount.

Make sure to replace `transaction_table` with the actual name of your transaction table in the database. When you execute this query, you should get the desired report with SSN and the corresponding net amount.

[mmezzeld]

Thank you very much.

Moe.