• Welcome, Guest. Please login.
 
November 21, 2019, 02:25:43 am

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - cj

1
Both routines are compared in this posting.

Bern,
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.

#INCLUDE "sqlitening.inc"

FUNCTION showit(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 slselary sql,sArray(),"Q9"
 FUNCTION = JOIN$(sArray(),$CR) + $CR
END FUNCTION

FUNCTION PBMAIN AS LONG
 bern
 cj
END FUNCTION

SUB InsertData
 slexe  "insert into T1 values(1,10),(1,20),(2,10),(4,100)"
 slexe  "insert into T2 values(1,30),(1,40),(2,10)"
 slexe  "insert into T3 values(1,-200),(2,0),(3,-1)"
END SUB

FUNCTION Bern AS LONG

 LOCAL s AS STRING
 KILL   "temp.db3":IF ERR THEN ? STR$(ERRCLEAR)
 slopen "temp.db3","C"

 slexe  "create table if not exists T1 (surname1 int,Amount1 int)"
 slexe  "create table if not exists T2 (surname2 int,Amount2 int)"
 slexe  "create table if not exists T3 (surname3 int,Amount3 int)"
 InsertData

 '? ShowIt("select * from t1") + $CR + ShowIt("select * from t2") + $CR + ShowIt("select * from t3")

 s=  "WITH"
 s+="   TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount1"
 s+="     FROM T1),"
 s+="   TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Amount2"
 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 as acct, ST1Total + ST2Total + ST3Total as amt"
 s+="   FROM SumT3"
 s+="   INNER JOIN SumT1 ON ST3Surname = ST1Surname"
 s+="   INNER JOIN SumT2 ON ST3Surname = ST2Surname"
 s+="   WHERE ST1Total + ST2Total <> ST3Total" 'made <> instead of subtract

 '? s,,"Sql"
 ?  ShowIt(s)
 'DIM sArray() AS STRING
 'slselAry s,sArray(),"Q9"
 '? JOIN$(sArray(),$CR),,FUNCNAME$
 slClose

END FUNCTION

FUNCTION CJ () AS LONG

 LOCAL sData(),sBind AS STRING, x AS LONG
 KILL   "temp.db3"
 slopen "temp.db3","C"

 slexe  "create table if not exists t1 (surname1 int,amt int)"     'charges table1
 slexe  "create table if not exists t2 (surname2 int,amt int)"     'charges table2
 slexe  "create table if not exists t3 (surname3 int,amt int)"     'payments
 slexe  "drop table if exists TempBalances
 slexe  "create table TempBalances(acct unique,amt)"
 InsertData

 'combine tables and payments
 slselary " select surname1,sum(amt),'t1' from t1 group by surname1"  +_     'charges table1
          " union "                                      +_
          " select surname2,sum(amt),'t2' from t2 group by surname2"  +_     'charges table2
          " union "                                      +_
          " select surname3,sum(amt),'t3' from t3 group by surname3",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(acct) DO update SET amt=amt+" + sData(2,x),sBind
 NEXT
 ? showit("select * from TempBalances"),,FUNCNAME$
 slclose
END FUNCTION
2
balances.png

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 LOCAL sData(),sBind AS STRING, x AS LONG

 KILL   "temp.db3"
 slopen "temp.db3","C"

 slexe  "create table if not exists t1 (acct,amt)"     'charges table1
 slexe  "create table if not exists t2 (acct,amt)"     'charges table2
 slexe  "create table if not exists t3 (acct,amt)"     'payments
 slexe  "drop table if exists TempBalances;create table TempBalances(acct unique,amt)"

 slexe  "insert into t1 values(1,1000),(2,2000),(3,3000)"             'charges t1
 slexe  "insert into t2 values(4,4000)"                               'charges t2
 slexe  "insert into t3 values(4,-2000),(1,-500),(2,-1000),(3,-1500)" 'apply payments

 'combine tables and payments
 slselary " select acct,sum(amt),'t1' from t1 group by acct"  +_     'charges table1
          " union "                                      +_
          " select acct,sum(amt),'t2' from t2 group by acct"  +_     'charges table2
          " union "                                      +_
          " select acct,sum(amt),'t3' from t3 group by acct",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(acct) DO update SET amt=amt+" + sData(2,x),sBind
 NEXT
 ? showit("select * from TempBalances"),,"Balances"
END FUNCTION

FUNCTION showit(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 slselary sql,sArray(),"Q9"
 FUNCTION = JOIN$(sArray(),$CR) + $CR
END FUNCTION
3
Need the relationships and what a detail line or the column heading should be.
Hosital Number  Client Number Invoice Number ....
4
https://sqlite.org/lang_with.html only for me.
This is a first for me, CTE is new have no idea what arsenal is?

I couldn't figure out the relational keys of your 3 tables.
key1
key2
key3

Do payments have to be applied to invoices for some unknown reason?
Is this a running system?
You make it hard for us.



5
Changed Amount to Amount1
Misuse of aggregate Total()

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL   "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Surname2 TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment       changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",     REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
LOCAL s AS STRING

s=  "WITH"
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+=" SELECT Surname3, Total( TT1Total) + Total( TT2Total) - Total(Amount3)"
s+="   FROM T3"
s+="   LEFT OUTER JOIN TempT1 ON Surname3 = TT1Surname"
s+="   LEFT OUTER JOIN TempT2 ON Surname3 = TT2Surname"
s+="   WHERE Total( TT1Total) + Total( TT2Total) > Total(Amount3)"
s+="   GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 'slSel s,0,"D"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
                                 
6
Error near FROM  (image at bottom)
Hope Fredrick needs this  :o
I'm going back to learning mode (about WITH)
I don't match payments to invoices so this is all new to me.

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL  "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Surname2 TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment      changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",    REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
LOCAL s AS STRING

s=  "WITH"
s+= "  TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount - Discount)"
s+="    FROM T1,"
s+="  TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Quantity * SellingPrice - Discount)"
s+="    FROM T2"
s+=" SELECT Surname3, Total( TT1Total) + Total( TT2Total) - Total(Amount3)"
s+="  FROM T3"
s+="  LEFT OUTER JOIN TempT1 ON Surname3 = TT1Surname"
s+="  LEFT OUTER JOIN TempT2 ON Surname3 = TT2Surname"
s+="  WHERE Total( TT1Total) + Total( TT2Total) > Total(Amount3)"
s+="  GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 'slSel s,0,"D"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
7
Almost accepted (incorrect use of aggregate Total()

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3

s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, Total(amount1) + TOTAL(Quantity * SellingPrice - t2.Discount)"
s+="  FROM t1,t2"
'       LEFT OUTER JOIN t2 ON t1.Surname1 = t2.surname2"
s+= "   WHERE surname1 = surname2"
s+="   GROUP BY surname2)"
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="   WHERE TotalBill > Total(Amount3)"
s+="   GROUP BY surname3

or using OUTER JOIN still returns incorrect use of Total()

SUB Bern
s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, Total(amount1) + TOTAL(Quantity * SellingPrice - t2.Discount)"
s+="  FROM t1"
s+="   LEFT OUTER JOIN t2 ON t1.Surname1 = t2.surname2"
s+="   GROUP BY surname2)"
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="   WHERE TotalBill > Total(Amount3)"
s+="   GROUP BY surname3"
8
I missed that incorrect naming SurnameName2 should be "Surname2".
It works now except incorrect use of aggregate Total().

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL   "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Surname2 TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment       changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",     REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3
s = " WITH Bills(Name,TotalBill) AS (SELECT t1.Surname1, t1.amount1 + TOTAL(Quantity * SellingPrice - t2.Discount)"
s+="  FROM t2,t1"
's+="   LEFT OUTER JOIN t2 ON t1.Surname1 = t2.surname2"
s+= "   WHERE surname1 = surname2"
s+="   GROUP BY surname2 )"
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = t3.surname3"
s+="   WHERE TotalBill > Total(t3.Amount3)"
s+="   GROUP BY t3.surname3"

 'DIM sArray() AS STRING
 'slselAry s,sArray(),"Q9"
 slSel s,0,"D"
 '? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
9
Ambiquous column name errors on variables after first select.
Removed the first with the error and the other are duplicates.
That may be need a few "AS".
Surname for a relational key doesn't seem very reliable.
Since I changed from statement may need to get rid of JOIN.

Added ( before first SELECT and ) before ) SELECT

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL   "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, SurnameName2 TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment       changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",     REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3
s = " WITH Bills(Name,TotalBill) AS (SELECT t1.Surname1, t1.amount1 + TOTAL(t2.Quantity * t2.SellingPrice - t2.Discount)"
s+="  FROM t1,t2"
s+="   LEFT OUTER JOIN t2 ON t1.Surname1 = t2.surname2"
s+="   GROUP BY t2.surname2 )"
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = t3.surname3"
s+="   WHERE TotalBill > Total(t3.Amount3)"
s+="   GROUP BY t3.surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
                              
10
I thought the system was under construction, but obviously already done.
Would have to see what the output is supposed to look like.  Nothing like I have seen.
I renamed tables to t1,t2,t3  to try to keep in brain. (services bill, cash/pharmacy bill, payments)
https://sqlite.org/lang_with.html  'need to study WITH

Bern,
I created some data (not good data for testing.)
Renamed amount and surname so they were not duplicates in table1 and table3
Get misuse of aggregate TOTAL().  Remarked Total and it runs.
Learning.  Never even knew about the WITH statement.

Fredrick,
Could you add a few records to have some relational data to test?

I find it a lot easier to use table  T1,T2,T3 while testing.

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL   "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Name TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment       changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",     REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("2","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("3","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3
s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, amount1 + TOTAL(Quantity * SellingPrice - t1.Discount)"
s+="  FROM t1,t3"  'ADDED T3
s+="   LEFT OUTER JOIN t2 ON Surname1 = surname3"
s+="   GROUP BY surname1) "
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="   WHERE TotalBill > Total(Amount3)"
s+="   GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB



#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL  "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Name TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment      changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",    REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("2","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("3","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3
s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, amount1 + TOTAL(Quantity * SellingPrice - t1.Discount)"
s+="  FROM t1,t3"  'ADDED T3 to get to work
s+="  LEFT OUTER JOIN t2 ON Surname1 = surname3"
s+="  GROUP BY surname1) "
s+="  SELECT Name,TotalBill FROM Bills"
s+="  LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="  WHERE TotalBill > Total(Amount3)"
s+="  GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
11
Sorry, I can't figure this one out.  I do in a different way.

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 slOpen "Need data"
 ReportDebtors
END FUNCTION

FUNCTION ReportDebtors() AS LONG
 slSel "With Bills(HospitalNo,TotalBill) As (Select HospitalNo, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
          "From tblCashSales " & _
          "Left OUTER JOIN tblBill On tblCashSales.HospitalNo = tblBill.HospitalNo " & _
          "GROUP BY HospitalNo" & _
      "Select HospitalNo From Bills " & _
          "Left OUTER JOIN tblPayments On Bills.HospitalNo = tblPayments.HospitalNo " & _
          "WHERE TotalBill > Total( tblPayments.Amount) " & _
          "GROUP BY HospitalNo"
    DO WHILE slGetRow()
      ? slf(1)
    LOOP
END FUNCTION
12
Post something with data and we can test it.
13
IncreaseBalance (ClientNumber&,BalanceType&,IncreaseAmount&)
Increases balance by amount passed.
It does not rename rowid's like "Insert or Replace Into."

Updated Friday, 11/8/2019 8:32 PM

#INCLUDE "sqlitening.inc"  'IncreaseBalance.bas

FUNCTION IncreaseBalance (ClientNumber AS LONG, BalType AS LONG,IncreaseAmount AS LONG) AS LONG
LOCAL sWhere AS STRING
sWhere = " Where ClientNumber=" + STR$(ClientNumber) + " and BalType=" + STR$(BalType)
slexe "Update BalanceTable Set Balance=Balance+"+ STR$(IncreaseAmount) + sWhere
IF ISFALSE(slGetChangeCount) THEN
  slexe USING$("Insert into BalanceTable values(#_,#_,#)",ClientNumber,BalType,IncreaseAmount)
END IF
END FUNCTION

FUNCTION PBMAIN () AS LONG
LOCAL x AS LONG
slOpen "Test.db3","C"
'slexe "drop table if exists BalanceTable"
slexe "create table if not exists BalanceTable(ClientNumber INTEGER,BalType INTEGER,Balance INTEGER)"
slexe "create unique index if not exists balance_idx  on BalanceTable(ClientNumber,BalType)"
slexe "create unique index if not exists balance_idx2 on BalanceTable(BalType,ClientNumber)"

slexe "begin immediate"
FOR x = 1 TO 1000
  IncreaseBalance 999,20,1 'client,type,increase by
  IncreaseBalance 123,82,1 'client,type,increase by
  IncreaseBalance 005,05,1 'client,type,increase by
NEXT
slexe "end"
? ViewAll("select RowId,ClientNumber as Client, BalType,Balance from BalanceTable order by ClientNumber")
END FUNCTION

FUNCTION ViewAll (sql AS STRING) AS STRING
LOCAL sArray() AS STRING
slSelAry sql,sArray(),"Q9"
FUNCTION = JOIN$(sArray(),$CR)
END FUNCTION
14
This should be easier to follow.

>I won't be able to track the previous transactions.
Tracking previous transactions shouldn't be hard.
The payments are already there in the payment table. This method doesn't apply payments to invoices.  It applies payments to the ClientTable balance.  If another table is needed for more tracking it should be easy to add one for a specific search. I tried to keep columns to a minimum and didn't add indexes.
I have always allowed a memo for each payment which has worked well.  Some use for check# or credit card#. Sometimes free-form is better.

Invoice file, important indexes:
Client + Invoice
Invoice + Client
1. Unposted invoices, or work in progress, are found for a client by clientkey = Client + 0
2. To find all unposted work search for invkey = 0

FUNCTION PBMAIN () AS LONG
 LOCAL clientnum,debit,credit AS LONG
 KILL $database:ERRCLEAR
 slopen $database,"C"
 slexe "create table if not exists ClientTable  (Cnum integer primary key,name text, balance integer,debits integer,credits integer)"
 slexe "create table if not exists InvoiceTable (Inum integer,invoice integer,debits integer)"
 slexe "create table if not exists PaymentTable (Pnum integer,paid integer)"

 'create 3 clients
 slexe "insert into ClientTable values(null,'Bern',0,0,0)","E0"
 slexe "insert into ClientTable values(null,'CJ',0,0,0)","E0"
 slexe "insert into ClientTable values(null,'Fredrick',0,0,0)","E0"

 'invoice client 1,2,3
 clientnum=1 : debit=100  : InsertUnposted(clientnum,debit)
 clientnum=2 : debit=100  : InsertUnposted(clientnum,debit)
 clientnum=3 : debit=100  : InsertUnposted(clientnum,debit)

 'post client 1,2,3 and apply an optional payment
 clientnum=1 : credit=101 : Postit(Clientnum,credit)
 clientnum=2 : credit=50  : Postit(Clientnum,credit)
 clientnum=3 : credit=100 : Postit(Clientnum,credit)

 DisplayBalances

END FUNCTION

FUNCTION InsertUnposted(cnum AS LONG,debit AS LONG) AS LONG
 slExeBind "insert into InvoiceTable values(?,?,?)",slBuildBindDat(STR$(cnum) ,"i") + slBuildBindDat(STR$(0)    ,"i") +slBuildBindDat(STR$(debit),"i")
END FUNCTION

SUB PostIt(cnum AS LONG,payment AS LONG)
 LOCAL debits AS LONG
 DIM s() AS STRING
 slexe "begin"
 IF payment THEN 'apply optional payment
  slexebind "insert into PaymentTable values(?,?)",slBuildBindDat(STR$(Cnum),"i") + slBuildBindDat(STR$(payment),"i")
  slexe "update ClientTable Set Credits = Credits +" + STR$(payment) + " where Cnum = " + STR$(Cnum)
 END IF
 slselAry "select sum(debits) from InvoiceTable where Inum=" + STR$(Cnum) +  " and invoice = 0",s(),"Q9c"
 debits = VAL(JOIN$(s(),""))
 slexe "update InvoiceTable Set Invoice = (Select Max(Invoice)+1 From InvoiceTable) Where Inum=" + STR$(cnum) + " and invoice = 0"
 slexe "update ClientTable Set Debits  = Debits +" + STR$(debits) + " where Cnum =" + STR$(cnum)
 slexe "update ClientTable Set Balance = Balance + Debits - Credits where cnum =" + STR$(cnum)
 slexe "end"
END SUB

FUNCTION DisplayBalances AS LONG
 LOCAL s,s() AS STRING
 slSelAry "select name as Patient,printf('%.2f',balance*.01) as Bal,printf('%.2f',debits*.01) as Debits," + _
          "printf('%.2f',credits*.01) as Credits from ClientTable",s(),"Q9"
 s+=JOIN$(s(),$CR) + $CR + $CR
 slselAry "select " +_
  " Case When Balance > 0 then name || ' , please remit '      || printf('%.2f',balance*.01)"  +_
  "      When Balance < 0 Then name || ' has overpaid '        || ABS(printf('%.2f',balance*.01))"  +_
  " else                      name || ' owes nothing'" +_
  " end as Balances from ClientTable",s(),"Q9c"
 s+=JOIN$(s(),$CR)
 ? s,, "Balance Report"
END FUNCTION         

Click image to enlarge
15
Not sure why scanning the transactions and payments is needed.
Apply payments to the balance of the client and done.
Is this for ageing?
Can't test because there is no data.

My demo:
Inv# = 0
client=1
insert InvoiceTable values (client + Inv#)  + data
update InvoiceTable Set Inv# = HighestInv where Inv# = 0 and clientnumber = client
update ClientTable Set Balance = debits+credits where clientnumber = client