Quote from: Fredrick Ughimi on December 10, 2019, 01:56:25 pm...
Yes, there is a master table I called tblBioData which contains the HospitalNo, Surname, Othernames, etc.
s+=" TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount1 - Discount"
s+=" FROM T1),"
s+=" TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Quantity * SellingPrice - Discount"
s+=" FROM T2),"
s+=" SumT1( ST1Surname, ST1Total) AS (SELECT TT1Surname, TOTAL( TT1Total)"
s+=" FROM TempT1 GROUP BY TT1Surname),"
s+=" SumT2( ST2Surname, ST2Total) AS (SELECT TT2Surname, TOTAL( TT2Total)"
s+=" FROM TempT2 GROUP BY TT2Surname),"
s+=" SumT3( ST3Surname, ST3Total) AS (SELECT Surname3, TOTAL( Amount3)"
s+=" FROM T3 GROUP BY Surname3)"
s+=" SELECT ST3Surname, ST1Total + ST2Total - ST3Total"
s+=" FROM tblBioData"
s+=" LEFT OUTER JOIN SumT1 ON tblBioData.Surname = ST1Surname"
s+=" LEFT OUTER JOIN SumT2 ON tblBioData.Surname = ST2Surname"
s+=" LEFT OUTER JOIN SumT3 ON tblBioData.Surname = ST3Surname"
s+=" WHERE ST1Total + ST2Total > ST3Total"
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