How to get the balance column in php lararavel?

I have a db table gl_trans

This is my expected output.

This is my source code so far.

        $start_date = (!empty($_POST["start_date"])) ? ($_POST["start_date"]) : ('');
        $data = $usersQuery
        ->select(
            'gl_trans.tran_date as date2', 
            'gl_trans.account as account2', //GET ACCCOUNT COLUMN
             DB::raw('(CASE WHEN gl_trans.amount >= 0 THEN gl_trans.amount ELSE 0 END) AS debit'), //GET DEBIT COLUMN
             DB::raw('(CASE WHEN gl_trans.amount < 0 THEN -gl_trans.amount ELSE 0 END) AS credit'), // GET CREDIT COLUMN
             DB::raw('(debit - credit) AS current_balance'),
             DB::raw('(SELECT sum(amount) FROM gl_trans WHERE tran_date < "'.$start_date.'" AND account = account2 and amount >= 0 ) AS debit_open_balance'), //SUM OF PREVIOUS DATE OF DEBIT VALUES (POSITIVE AMOUNT)
             DB::raw('(SELECT sum(amount) FROM gl_trans WHERE tran_date < "'.$start_date.'" AND account = account2 and amount < 0 ) AS credit_open_balance'), //SUM OF PREVIOUS DATE OF CREDIT VALUES (NEGATIVE AMOUNT)
             DB::raw('( SELECT (CASE 
                WHEN current_balance >= 0 
                THEN (COALESCE(debit_open_balance, 0) - COALESCE(-credit_open_balance, 0) + current_balance) 
                ELSE (COALESCE(debit_open_balance, 0) - COALESCE(-credit_open_balance, 0) - current_balance) end)) as balance') // GET BALANCE COLUMN USING THE CURRENCT BALANCE 
         ) 
        ->orderBy('date2', 'ASC')
       ->get();

My problem is that I can't get the exact value of debit_open_balance and credit_open_balance using the $star_date as it should get the tran_date of the row but it is not unique. I have tried using the id > current_id but it will not get the exact amount because the data is being ordered as date.

Note: Opening balance is the (total positive amount - total negative amount) of the previous date (< $start_date)

Please help thank you.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2ocncAO
via IFTTT