Quote from: Fredrick Ughimi on December 10, 2019, 10:14:35 am...
>>Bern routine results: 1 payment record must exist to return results for a client
I also noticed that all three tables needs to have data for a client to return results for that client.
'Both routines are compared in this posting.
'Nice, learned a lot!!
'Bern routine results:
'1 payment record must exist to return results for a client
'2 credit balances do not display (they weren't asked for)
'3 optional, modified to add payments (passed as negative amounts)
'The CJ routine could eliminate the creating temp table by reading
'through the recordset and handling the debit/credit as they occur.
'Made it easier by just creating a temp file with the balances.
'Also, for some reason when I dropped the what I thought was unneeded third column in the
'union statement t1,t2,t3 the union was not always correct. Need to look into that.
'Leaving "as is" is suggested.
'In this example CJ will return 4-records and Bern will return 2-records.
'Fredrick, these differences should be considered.
FUNCTION PBMAIN () AS LONG
LOCAL sData(),sBind AS STRING, x AS LONG
slexe "drop table if exists TempBalances;create table TempBalances(HospitalNo unique,Amount)"
'combine tables and payments
slselary " select HospitalNo,SUM(Amount),'tblBill' from tblBill group by HospitalNo" +_ 'charges table1
" union " +_
" select HospitalNo,SUM(SellingPrice * Quantity - Discount) as Amount,'tblCashSales' from tblCashSales group by HospitalNo" +_ 'charges table2
" union " +_
" select HospitalNo,SUM(Amount),'tblPayments' from tblPayments group by HospitalNo",sData() 'payments table3
'update TempBalances() from above sData()
FOR x = 1 TO UBOUND(sdata,2)
sBind = slBuildBindDat(sdata(1,x),"T") + slBuildBindDat(sData(2,x),"T")
slexebind "Insert into TempBalances values(?,?) ON conflict(HospitalNo) DO update SET Amount=Amount+" + sData(2,x),sBind
? showit("select * from TempBalances"),,"Balances"
FUNCTION showit(sql AS STRING) AS STRING
LOCAL sArray() AS STRING
FUNCTION = JOIN$(sArray(),$CR) + $CR