John John - 5 months ago 7
SQL Question

How can I get the BALANCE for every row using the query below

I want to get the balance for every Debit and Credit then calculate the balance per row..

SELECT SUM(IF(s.AMT>0 AND s.SLE_CODE=11,s.AMT,0)) AS DEBIT,
SUM(IF(s.AMT<0 AND s.SLE_CODE=11,s.AMT,0)) * -1 AS CREDIT
FROM
sldtl AS s
LEFT JOIN
transtype
ON
transtype.TransTypeID = s.TR_CODE
WHERE
s.SL_BRCODE= 1 AND s.SL_CLIENTID= 267 AND s.SLC_CODE= 13 AND s.SLT_CODE= 15 AND
s.REF_NO= s.REF_NO AND s.TR_DATE >= (SELECT s.TR_DATE FROM sldtl AS s ORDER BY s.TR_DATE DESC LIMIT 1)-INTERVAL 6 MONTH
GROUP BY
s.TR_DATE DESC, s.TR_CODE, s.TR_CTLNO


needed result

+-----------+--------------+---------------+
| DEBIT | CREDIT | BALANCE |
+-----------+--------------+---------------+
| 0.00 | 700.00 | -700.00 |
| 0.00 | 700.00 | -1400.00 |
| 400.00 | 0.00 | -1000.00 |
| 2000.00 | 500.00 | 500.00 |
+-----------+--------------+---------------+

Answer

Let's consider the simplified version of your problem:

A table containing only debit and credit columns. And you want to calculate the balance column:

balanceTable:

 debit    credit
   0        700 
   0        700
 400          0
2000        500


SELECT 
debit,
credit,
@balance := @balance + debit - credit AS balance
FROM balancetable, (SELECT @balance := 0) var;

SQL FIDDLE DEMO

So in your case it would be like:

SELECT 
t.DEBIT,
t.CREDIT,
@balance := @balance + t.DEBIT - t.CREDIT AS BALANCE
FROM 
(
    SELECT SUM(IF(s.AMT>0 AND s.SLE_CODE=11,s.AMT,0)) AS DEBIT,
                 SUM(IF(s.AMT<0 AND s.SLE_CODE=11,s.AMT,0)) * -1 AS CREDIT
    FROM
        sldtl AS s
    LEFT JOIN
        transtype
    ON
        transtype.TransTypeID = s.TR_CODE
    WHERE
        s.SL_BRCODE= 1 AND s.SL_CLIENTID= 267 AND s.SLC_CODE= 13 AND s.SLT_CODE= 15 AND 
        s.REF_NO= s.REF_NO AND s.TR_DATE >= (SELECT s.TR_DATE FROM sldtl AS s ORDER BY s.TR_DATE DESC LIMIT 1)-INTERVAL 6 MONTH
    GROUP BY
        s.TR_DATE DESC, s.TR_CODE, s.TR_CTLNO 
) AS t , (SELECT @balance := 0) var;