php - How to fetch sum of amount with negative sign ms sql server

[ad_1]

I have been trying to fetch sum of amount from MemTrans table by fetching amounts with no zeros but everytime I tried to write a query with amount < 0 it returns no results for the outstanding balance.

Here is a screenshot for more understanding of what I mean

If you take a look at the image you will see Outstanding Balance with zero values appearing after i fetched the sum of amount from the MemTrans table but i don't want the zeros and also get only people with Credit not Debit. From the Memtrans table the accounts with minus(amount) is what i really want to get.

Belows is my script for querying and fetching it.

<?php


 $ques = "select * from company";
 $checks22y = sqlsrv_query($conn, $ques);
 $row22y = sqlsrv_fetch_array($checks22y, SQLSRV_FETCH_ASSOC);
 $daty = $row22y['BRSES_DATE']->format('Y-m-d H:m:i');
 $com = $row22y['branch'];
 $query = 
 "SELECT ".$limitresult." Member.Branch,Member.GL_No,Member.Ac_NO,Member.BRANCH+Member.GL_NO+Member.AC_NO 
AS BRGLAC,Customer.Cust_No,Customer.Name,Group_Name,ID_CARD,Subgroup as subgroup2,
Cust_Type,Cust_Sex,Cust_Cat,Area_Code,Cust_Type,Dobirth,Address,Ref_No,Bank_VNO,Cust_Ca2,
nType,Group_Code FROM Member LEFT JOIN CUSTACC ON Member.Branch = CustAcc.Branch AND 
Member.GL_NO = CustACC.GL_No AND Member.AC_NO = CustACC.AC_No LEFT JOIN Customer ON 
Member.Branch = Customer.Branch".$branchid." AND Member.Cust_No = Customer.Cust_No ".$accnos." WHERE 
CUSTACC.Exp_Date < '$daty' AND MEMBER.Gl_NO IN 
(SELECT Coa.GL_NO FROM Coa WHERE Product = 'S' ) AND cust_type IN ('IND','GRP','MEM') ";


 $check = sqlsrv_query($conn, $query);
 $i = 1;
 while($rows = @sqlsrv_fetch_array( $check, SQLSRV_FETCH_ASSOC)) {

 $ac = $rows['Ac_NO'];



 $get ="SELECT Memtrans.Branch,Memtrans.GL_NO,Memtrans.AC_NO,BRANCH+GL_NO+AC_NO AS BRGLAC,SUM(Amount) AS OutBalance, MAX(Batch_Date) AS Last_Trx2 FROM MemTrans WHERE Memtrans.Branch = '$com' 
 AND ac_no = '$ac' AND ERR_FLAG <> 'E' AND Memtrans.Gl_NO IN (SELECT Coa.GL_NO FROM Coa WHERE Product = 'S' ) 
 GROUP BY Memtrans.Branch,Memtrans.GL_NO,Memtrans.AC_NO";
 $check2 = sqlsrv_query($conn, $get); 
 $rowb = sqlsrv_fetch_array( $check2, SQLSRV_FETCH_ASSOC);

 //$gets ="SELECT sum(amount) as outamt from Memtrans where Branch = '$com' 
 //AND ac_no = '$ac' AND ERR_FLAG <> 'E' 
 //GROUP BY Branch, GL_NO,ac_no";
 //$check2s = sqlsrv_query($conn, $gets); 
 //$rowbs = sqlsrv_fetch_array( $check2s, SQLSRV_FETCH_ASSOC);


 //OD history
 $od = "select branch, od_limit, exp_date from custacc where ac_no='$ac'";

 $odc = sqlsrv_query($conn, $od); 
 $rowbo = sqlsrv_fetch_array( $odc, SQLSRV_FETCH_ASSOC);

 //$e = "select branch, ac_no, gl_no from MemTrans where ac_no='$ac'";
 //$a = sqlsrv_query($conn, $e);
 //$lol = sqlsrv_fetch_array($a, SQLSRV_FETCH_ASSOC);


 ?>

[ad_2]

لینک منبع