Hello,
I am trying to figure out how one can get the list of debtors from the three tables below using the SQLite Statement.
Insignt:
TotalBill = ServicesBill (Amount) + tblPharmacyBill (Quantity*SellingPrice-Discount)
If TotalBill > Payments (Amount) then
? "Patient is a debtor"
Else
? "Patient not a debtor"
End If
'ServicesBill
slExe "Create Table If Not Exists tblServicesBill(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname TEXT, Othernames TEXT, Category TEXT," & _
"Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, Amount REAL, Discount REAL, Status TEXT)"
'Pharmacy Bill
slExe Build$("Create Table If Not Exists tblPharmacyBill(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
slExe Build$("Create Table If Not Exists tblPayments(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname TEXT, Othernames TEXT,", _
"Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")
Any pointers would be appreciated. Thank you.
I believe that you can get the list in one single SQL SELECT statement. Something like this should work (untested):
WITH Bills(Name,TotalBill) AS (SELECT Surname, Total(Amount) + Total( Quantity * SellingPrice - Discount)
FROM tblServicesBill
LEFT OUTER JOIN tblPharmacyBill ON tblServicesBill.Surname = tblPharmacyBill.Surname
GROUP BY Surname
SELECT Name FROM Bills
LEFT OUTER JOIN tblPayments ON Bills.Name = tblPayments.Surname
WHERE TotalBill > Total( tblPayments.Amount)
GROUP BY Surname
Wow! Thank you CJ and Been.
I would try both suggested solutions and get back to you.
Regards.
Quote from: undefinedTotalBill = ServicesBill (Amount) + tblPharmacyBill (Quantity*SellingPrice-Discount)
If TotalBill > Payments (Amount) then
? "Patient is a debtor"
Else
? "Patient not a debtor"
End If
The above code does not take into consideration the previous client balance.
Added a balance column into a ClientTable, posted transactions, updated balance.
Debtors will have a balance > 0. Used a CASE statement to display message and the balance.
Also, demonstrates using pennies for all money to avoid any rounding error.
slselAry "select cnum," +_
" Case When Balance > 0 then 'Patient is a debtor ' || printf('%.2f',balance*.01)" +_
" When Balance < 0 Then 'Patient credit balance ' || ABS(printf('%.2f',balance*.01))" +_
" else 'Patient no balance'" +_
" end as Patient_Balance_Report from ClientTable",s(),"Q9"
s+=JOIN$(s(),$CR)
Another CASE statement that displays client number, message and balance without column alignment or column names.
"Q9c" option was needed with slselAry to get rid of the column name.
slselAry "select " +_
" Case When Balance > 0 then 'Client '|| cnum || ' balance ' || printf('%.2f',balance*.01)" +_
" When Balance < 0 Then 'Client '|| cnum || ' credit ' || ABS(printf('%.2f',balance*.01))" +_
" else 'Client ' || cnum || ' no balance'" +_
" end as Balances from ClientTable",s(),"Q9c"
See last 3-lines of the below image
Hello Bern,
Wrapping the SQL statement in SQLitening is giving error near the second select statement.
slSel "With Bills(Name,TotalBill) As (Select Surname, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
"From tblBill " & _
"Left OUTER JOIN tblPharmacyBill On tblCashSales.Surname = tblPharmacyBill.Surname " & _
"GROUP BY Surname" & _
"Select Name From Bills " & _
"Left OUTER JOIN tblPayments On Bills.Name = tblPayments.Surname " & _
"WHERE TotalBill > Total( tblPayments.Amount) " & _
"GROUP BY Surname"
I think you need to add a space character after "Surname" in line 4:
"GROUP BY Surname " & _
I did that. Same error.
Hello CJ,
Your solution is kinda complicated for me to follow and implement.
Hello Bern,
I am new to CTEs. I am trying to wrap my head around it. I think its a powerful technique.
>>Not sure why scanning the transactions and payments is needed.
The tblPayments is used to store patients payments against the bills on services (tblBill) and pharmacy (tblCashSales). Installment payments are allowed to clear up the bills.
>>slexe "create table ClientTable (Cnum integer,name text, balance integer,debits integer,credits integer)
After further study of your codes, I now understand what you were doing. It makes sense now. But if I create the ClientTable now and insert the values to it as both Billing tables and Payment tables are used, I won't be able to track the previous transactions.
Quote from: Fredrick Ughimi on November 07, 2019, 06:02:17 PM...
The tblPayments is used to store patients payments against the bills on services (tblBill) and pharmacy (tblCashSales). Installment payments are allowed to clear up the bills.
Looks like you changed some of the table names from what you posted in the OP. Please make sure that all the table names in the code are correct:
slSel "With Bills(Name,TotalBill) As (Select Surname, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
"From ***tblBill*** " & _
"Left OUTER JOIN tblPharmacyBill On ***tblCashSales***.Surname = tblPharmacyBill.Surname " & _
"GROUP BY Surname" & _
"Select Name From Bills " & _
"Left OUTER JOIN tblPayments On Bills.Name = tblPayments.Surname " & _
"WHERE TotalBill > Total( tblPayments.Amount) " & _
"GROUP BY Surname"
*** => should be the same table - whichever is the correct table name.
Hello Bern,
The real names of the tables are not the ones I mentioned in my OP. I changed them in my OP just to depict what they do, so others can easily understand.
tblPharmacyBill is tblCashSales (Real Name)
tblPayments is tblPayments (Same Name)
tblServicesBill is tblBill (Real Name)
It still would not compile.
Thanks.
Hello Bern,
Thank you.
The table name are in the correct order in my version. I used the HospitalNo field in place of the Surname because it is common to all the three tables. I get an error near the bolded HospitalNo. It was the same error with your codes (Name).
Your Code: Error in the bolded "Name"
slSel "With Bills(Name,TotalBill) As (Select Surname, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
"From tblBill " & _
"Left OUTER JOIN tblBill On tblCashSales.Surname = tblBill.Surname " & _
"GROUP BY Surname" & _
"Select [b]Name[/b] From Bills " & _
"Left OUTER JOIN tblPayments On Bills.Name = tblPayments.Surname " & _
"WHERE TotalBill > Total( tblPayments.Amount) " & _
"GROUP BY Surname"
My version with the corect tables in place. Error near the bolded HospitalNo
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 [b]HospitalNo[/b] From Bills " & _
"Left OUTER JOIN tblPayments On Bills.HospitalNo = tblPayments.HospitalNo " & _
"WHERE TotalBill > Total( tblPayments.Amount) " & _
"GROUP BY HospitalNo"
This method would work best for me if I could it to run.
Best regards.
Post something with data and we can test it.
CJ, Check attached.
#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"
#INCLUDE "ddoc_p32.inc"
'DDOC
' Needed Color Constants (Vb compatible)
'---------------------------------------------------------------------------
%vbBlack = &H0&
%vbRed = &HFF&
%vbGreen = &HFF00&
%vbYellow = &HFFFF&
%vbBlue = &HFF0000
%vbMagenta = &HFF00FF
%vbCyan = &HFFFF00
%vbWhite = &HFFFFFF
%vbGrey = &HC0C0C0
%LIGHT_SHADE = &HE0E0E0
FUNCTION PBDate(sDate AS STRING) AS STRING
'MM-dd-yyyy to dd-MM-yyyy
FUNCTION = MID$(sDate,4,2) & "-" & MID$(sDate,1,2) & "-" & MID$(sDate,7,4)
END FUNCTION
FUNCTION NigeriaDate(sDate AS STRING) AS STRING
'yyyy-MM-dd to dd-MM-yyyy
FUNCTION = MID$(sDate,9,2) & "-" & MID$(sDate,6,2) & "-" & MID$(sDate,1,4)
END FUNCTION
FUNCTION SQLiteDate(sDate AS STRING) AS STRING
'From dd-MM-yyyy To yyyy-MM-dd
FUNCTION = MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2)
END FUNCTION
FUNCTION PBMAIN () AS LONG
slOpen ("HospitalProDB.db3", "C")
ReportDebtors
END FUNCTION
FUNCTION ReportDebtors() AS LONG
LOCAL dbHandle&, LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
LOCAL detailline AS ASCIIZ * 200
LOCAL SNo AS LONG
LOCAL lTotalRecords&
LOCAL sFrom AS STRING
LOCAL sTo AS STRING
LOCAL dTotalAmount AS DOUBLE
LOCAL dNetAmount AS DOUBLE
LineSpacing = .2
TopMargin = .5
PAGESIZE = 10
ihandle% = dpStartDoc(0,"Mega-Net HospitalPro - Debtors Report","",%DDOC_INCH, %DDOC_PAPER_A4, %DDOC_LANDSCAPE, %DDOC_SYSTEM_DEFAULT, %DDOC_BIN_AUTO OR %DDOC_ALLOWSMTP OR %DDOC_ALLOWSAVE OR %DDOC_ZOOMFIT)
IF ihandle < 1 THEN
MSGBOX "Could not StartDoc. Error number" + STR$(Ihandle)
GOTO EndProgram
END IF
dpSetTabs iHandle, "L.5W1 L1.W1 L2.8W1 L5.4W2"
ROW = PAGESIZE 'force new heading
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()
IF ROW => PAGESIZE THEN
IF PageNumber THEN 'If first page don't need a new page
dpNewPage iHandle%, %ddoc_PAPER_A4, %DDOC_LANDSCAPE, %DDOC_BIN_AUTO
END IF
INCR PageNumber
LOCAL hjpg AS LONG
hjpg = dpAddGraphic(ihandle%, EXE.PATH$ & "Logo.jpg")
IF hjpg THEN
dpDrawGraphic ihandle%, hjpg,5.5,.2,2,1
END IF
dpText iHandle%, .5,TopMargin, %DDOC_Left,"Page: "+ STR$(PageNumber)+""
dpText iHandle%, .5,TopMargin + .2, %DDOC_Left,"Total Records: "+ STR$(lTotalRecords&)+""
dpText iHandle%, 10,TopMargin, %DDOC_Left,"Date: "+ PBDate(DATE$)+""
dpText iHandle%, 10,TopMargin +.2, %DDOC_Left,"Time: "+ TIME$+""
ROW =TopMargin + LineSpacing * 4
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbGreen, "Arial"
'dpText ihandle, 6.25, 1.1, %DDOC_CENTER, "" & gName
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
'dpText IHandle%, 6.25, 1.4, %DDOC_CENTER, "" & gAddress
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
dpText IHandle%, 6.25, 1.7, %DDOC_CENTER, "Debtors Report"
dpText IHandle%, 6.25, 2.0, %DDOC_CENTER, "Date Range: " & sFrom & " To " & sTo
dpText IHandle%, .5, 2.3, %DDOC_Left, "Total Amount: " & FORMAT$(dTotalAmount, "#,.00")
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 9, %vbBlack, "Arial"
dpTabText iHandle%, ROW + 1.3, "S/No" + $TAB + "HNO" + $TAB + "Surname" + $TAB + "Othernames"
dpFont ihandle%, %DDOC_FONTNORMAL, 8, %vbBlack, "San Serif"
ROW = ROW + LineSpacing * 2
END IF
DetailLine$ = _
STR$(SNo) + $TAB + _
slFN("HospitalNo") + $TAB + _
slFN("Surname") + $TAB + _
slFN("Othernames")
dpTabText iHandle%, ROW + 1.1, DetailLine
ROW = ROW + LineSpacing 'increment line counter
LOOP
dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
EndProgram:
END FUNCTION
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
Thanks CJ. I would give it a shot in the morning.
CJ, Same error near HospitalNo
#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
slOpen ("HospitalProDB.db3", "C")
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 [b]HospitalNo[[/b] 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
You both need to add a space at the end of the first "GROUP BY HospitalNo" string segment.
... GROUP BY HospitalNo Select [b]HospitalNo[[/b] ...
*not* (as you have posted above):
... GROUP BY HospitalNoSelect [b]HospitalNo[[/b] ...
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
cj, I'm not near my work (compiling) computer, so I can't test, but it looks to me like your latest test code can (and should) be tweaked:
...
'Pharmacy Bill changed to t2 (** changed "Name" to "Surname2" **)
slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Surname2 TEXT,", _
...
s+=" FROM t1" '**Removed the** ADDED T3
s+=" LEFT OUTER JOIN t2 ON Surname1 = Surname2" 'Surname2 is in t2
...
Also, it's not entirely clear, but I thought the discount field referenced in the total() function was also in t2. I thought all three fields ( Quantity, SellingPrice, Discount ) were being pulled from t2. Only amount1 was being pulled from t1 for the TotalBill calculation.
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
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
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"
I wasn't sure if TOTAL(Quantity * SellingPrice - t2.Discount) would work as I was expecting. If that is what SQLite is choking on, the statement will need to be reworked a bit. Something like this:
WITH
TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount - Discount)
FROM T1,
TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Quantity * SellingPrice - Discount)
FROM T2
SELECT Surname3, Total( TT1Total) + Total( TT2Total) - Total(Amount3)
FROM T3
LEFT OUTER JOIN TempT1 ON Surname3 = TT1Surname
LEFT OUTER JOIN TempT2 ON Surname3 = TT2Surname
WHERE Total( TT1Total) + Total( TT2Total) > Total(Amount3)
GROUP BY surname3
I noticed that OP's table definitions included a discount field in T1, but it wasn't included in the desired logic. Above code assumes that was an oversight and any discounts in T1 should be considered in the calculations.
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
Ooops (red face). I misplaced the end parenthesis on the WITH statements....
WITH
TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount - Discount
FROM T1),
TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Quantity * SellingPrice - Discount
FROM T2)
...
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
Hello,
What are you guys reading to understand CTEs apart from the official documentation? Is it a new feature added to SQLite? It seems CTEs are powerful arsenal (a supporter of Arsenal by the way) to have.
Kind regards.
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.
Sqlite3 supports CTEs from version 3.8.3 (see https://www.sqlite.org/changes.html). Not sure what SQLite DLL I have installed. I would check later on.
CJ,
The relational key to all three tables is the HospitalNo and in a many to many relationship.
>>Do payments have to be applied to invoices for some unknown reason?
Yes. Since the hospitals allows installment payments. Invoices are not completely paid for by the patient. Invoices carries the total bill, while the Payment Received table carries the amount paid by the patient.
>>Is this a running system?
Yes, it is.
Need the relationships and what a detail line or the column heading should be.
Hosital Number Client Number Invoice Number ....
OK. I played with cj's code and found the problem. You cannot use Total() in a WHERE clause. I reworked the statement and it compiles/works now.
#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 REAL, 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+=" 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 SumT3"
s+=" INNER JOIN SumT1 ON ST3Surname = ST1Surname"
s+=" INNER JOIN SumT2 ON ST3Surname = ST2Surname"
s+=" WHERE ST1Total + ST2Total > ST3Total"
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
The output doesn't show anything because it's computing zero for TT1Total and TT2Total with the dummy data in this code. You can remove the WHERE clause from the statement to see it's computing -1 for ST1Total + ST2Total - ST3Total.
This statement flattens all three tables into a single record per Surname/HospitalNo. The final SELECT is using T3 as the leftmost FROM table, so the output will only list Surname/HospitalNo records if a record exists in (Sum)T3. If you want results where no record (for Surname/HospitalNo) exists in T3, you would need to adjust the statement to base the results on either SumT1 or SumT2.
I'm not sure how to combine the output to list every Surname/HospitalNo record that exists across all three tables with the db schema as listed. If you have another table with a master list of Surname/HospitalNo records, you could use that as the base and use LEFT OUTER JOINS on SumT1, SumT2 and SumT3.
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
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
Hello CJ & Bern,
Thank you for helping out to find a solution to this.
I think I would along with Bern's version as I have been able to add more fields to it and implement it.
I haven't been able to make CJ's version work for me. I still intend to take another look at it later on.
Thank you guys, I appreciate your efforts and I have learnt a lot from this.
Best regards.
Hello Cj,
I tried implementing your version today:
#COMPILE EXE
#DIM ALL
'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 PBMAIN () AS LONG
LOCAL sData(),sBind AS STRING, x AS LONG
slopen "HospitalProDB.db3","C"
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
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
I got incomplete input error.
>>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.
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.
That's because I used INNER JOINs. You can use LEFT OUTER JOINS if you want results from partial matches, but even so, the table referenced after FROM will control the results set, so the results only return for records in that table.
Like I said previously, if you have a fourth table that contains a master list of the Surnames/HopsitalNos, you can use that as the FROM reference and LEFT OUTER JOIN the 3 "SUM" tables from the WITH clause and get a full result set.
Hello Bern,
>>That's because I used INNER JOINs. You can use LEFT OUTER JOINS if you want results from partial matches, but even so, the table referenced after FROM will control the results set, so the results only return for records in that table.
Thank you for your explanations.
>>Like I said previously, if you have a fourth table that contains a master list of the Surnames/HopsitalNos, you can use that as the FROM reference and LEFT OUTER JOIN the 3 "SUM" tables from the WITH clause and get a full result set.
Yes, there is a master table I called tblBioData which contains the HospitalNo, Surname, Othernames, etc.
I will follow your instructions and see how it goes. Thanks a lot.
Hello CJ,
>>The dummy columns t1,t2,t3 are required to get the correct results. Please test!
I will test it as soon as I am with my computer. Thanks a lot.
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.
...
Then this should work (adjust table names to match your code):
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+=" 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"
Hello Bern,
>>Then this should work (adjust table names to match your code):
This worked. I am going to test further in the days to come.
Thanks a million.
s= "WITH"
s+=" TempT1(TT1HospitalNo,Surname, Othernames,TT1Total) AS (SELECT HospitalNo, Surname, Othernames, Amount - Discount"
s+=" FROM tblBill),"
s+=" TempT2(TT2HospitalNo,Name,TT2Total) AS (SELECT tblCashSales.HospitalNo, Name, Quantity * SellingPrice - Discount"
s+=" FROM tblCashSales),"
s+=" SumT1( ST1HospitalNo, ST1Total) AS (SELECT TT1HospitalNo, TOTAL( TT1Total)"
s+=" FROM TempT1 GROUP BY TT1HospitalNo),"
s+=" SumT2( ST2HospitalNo, ST2Total) AS (SELECT TT2HospitalNo, TOTAL( TT2Total)"
s+=" FROM TempT2 GROUP BY TT2HospitalNo),"
s+=" SumT3(ST3HospitalNo, Surname, Othernames, ST3Total) AS (SELECT tblPayments.HospitalNo, Surname, Othernames, TOTAL(Amount)"
s+=" FROM tblPayments GROUP BY tblPayments.HospitalNo)"
s+=" SELECT tblBioData.HospitalNo, tblBioData.Surname, tblBioData.Othernames, ST1Total + ST2Total - ST3Total"
s+=" FROM tblBioData"
s+=" LEFT OUTER JOIN SumT1 ON tblBioData.HospitalNo = ST1HospitalNo"
s+=" LEFT OUTER JOIN SumT2 ON tblBioData.HospitalNo = ST2HospitalNo"
s+=" LEFT OUTER JOIN SumT3 ON tblBioData.HospitalNo = ST3HospitalNo"
s+=" WHERE ST1Total + ST2Total > ST3Total"
Aside: Is it possible to get the:
1 = near ""WITH": syntax error
on one system and not get it on another system? One of my clients had this error, but it worked fine on my system.
Hi CJ,
I tried your code directly against my database. It compiled and ran without showing any result.
#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
slopen "HospitalProDB.db3","C"
LOCAL sData() AS STRING
slselary " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill group by HospitalNo" +_
" UNION" +_
" select HospitalNo,SUM(SellingPrice*Quantity-Discount),'tblCashSales' from tblCashSales group by HospitalNo" +_
" UNION" +_
" select HospitalNo,SUM(Amount), 'tblPayments' from tblPayments group by HospitalNo",sData()
slselary " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill group by HospitalNo UNION " +_
" select HospitalNo,SUM(SellingPrice * Quantity - Discount),'tblCashSales' from tblCashSales group by HospitalNo UNION " +_
" select HospitalNo,SUM(Amount), 'tblPayments' from tblPayments group by HospitalNo",sData()
'without temp table
LOCAL x,linecounter AS LONG
DIM hospital(999) AS LONG
DIM sArray(999) AS STRING
FOR x = 1 TO UBOUND(sdata,2)
hospital(VAL(sdata(1,x))) += VAL(sdata(2,x)) 'combine amounts in column2
NEXT
sArray(0) = "Hospital Amount" 'optional heading
FOR x = 1 TO UBOUND(hospital) 'hospital 1 to ?
IF hospital(x) THEN 'if hospital <> 0
INCR linecounter
sArray(linecounter) = USING$("#&##.##",x,$TAB,hospital(x)*.01)
'sArray(linecounter) = FORMAT$(x) + $TAB + USINg$("##.##",hospital(x)*.01)
END IF
NEXT
REDIM PRESERVE sArray(linecounter)
? JOIN$(sArray(),$CR),,"Balances"
END FUNCTION
I tried again using DDOC this time and discovered I could retrieve the balances.
#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"
#INCLUDE "ddoc_p32.inc"
'DDOC
' Needed Color Constants (Vb compatible)
'---------------------------------------------------------------------------
%vbBlack = &H0&
%vbRed = &HFF&
%vbGreen = &HFF00&
%vbYellow = &HFFFF&
%vbBlue = &HFF0000
%vbMagenta = &HFF00FF
%vbCyan = &HFFFF00
%vbWhite = &HFFFFFF
%vbGrey = &HC0C0C0
%LIGHT_SHADE = &HE0E0E0
FUNCTION PBMAIN () AS LONG
slOpen ("HospitalProDB.db3", "C")
ReportDebtors
END FUNCTION
FUNCTION PBDate(sDate AS STRING) AS STRING
'MM-dd-yyyy to dd-MM-yyyy
FUNCTION = MID$(sDate,4,2) & "-" & MID$(sDate,1,2) & "-" & MID$(sDate,7,4)
END FUNCTION
FUNCTION NigeriaDate(sDate AS STRING) AS STRING
'yyyy-MM-dd to dd-MM-yyyy
FUNCTION = MID$(sDate,9,2) & "-" & MID$(sDate,6,2) & "-" & MID$(sDate,1,4)
END FUNCTION
FUNCTION SQLiteDate(sDate AS STRING) AS STRING
'From dd-MM-yyyy To yyyy-MM-dd
FUNCTION = MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2)
END FUNCTION
FUNCTION ReportDebtors() AS LONG
LOCAL dbHandle&, LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
LOCAL detailline AS ASCIIZ * 200
LOCAL SNo AS LONG
LOCAL lTotalRecords&
LOCAL dTotalAmount AS DOUBLE
LOCAL dNetAmount AS DOUBLE
LOCAL s AS STRING
LOCAL dTotalDebts, dTotalBill, dTotalPayment AS DOUBLE
LineSpacing = .2
TopMargin = .5
PAGESIZE = 10
ihandle% = dpStartDoc(0,"Mega-Net HospitalPro - Debtors Report","",%DDOC_INCH, %DDOC_PAPER_A4, %DDOC_PORTRAIT, %DDOC_SYSTEM_DEFAULT, %DDOC_BIN_AUTO OR %DDOC_ALLOWSMTP OR %DDOC_ALLOWSAVE OR %DDOC_ZOOMFIT)
IF ihandle < 1 THEN
MSGBOX "Could not StartDoc. Error number" + STR$(Ihandle)
GOTO EndProgram
END IF
dpSetTabs iHandle, "L.5W1 L1.0W1 L2.5W3 L5.5W1 L6.5W1 L7.5W1"
ROW = PAGESIZE 'force new heading
slSEL " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill group by HospitalNo" +_
" UNION" +_
" select HospitalNo,SUM(SellingPrice*Quantity-Discount),'tblCashSales' from tblCashSales group by HospitalNo" +_
" UNION" +_
" select HospitalNo,SUM(Amount), 'tblPayments' from tblPayments group by HospitalNo"
slSEL " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill group by HospitalNo UNION " +_
" select HospitalNo,SUM(SellingPrice * Quantity - Discount),'tblCashSales' from tblCashSales group by HospitalNo UNION " +_
" select HospitalNo,SUM(Amount), 'tblPayments' from tblPayments group by HospitalNo"
DO WHILE slGetRow()
IF ROW => PAGESIZE THEN
IF PageNumber THEN 'If first page don't need a new page
dpNewPage iHandle%, %ddoc_PAPER_A4, %DDOC_PORTRAIT, %DDOC_BIN_AUTO
END IF
INCR PageNumber
LOCAL hjpg AS LONG
''hjpg = dpAddGraphic(ihandle%, VD_App.Path & "Logo.jpg")
IF hjpg THEN
dpDrawGraphic ihandle%, hjpg,3.5,.2,2,1
END IF
dpText iHandle%, .5,TopMargin, %DDOC_Left,"Page: "+ STR$(PageNumber)+""
'dpText iHandle%, .5,TopMargin + .2, %DDOC_Left,"Total Records: "+ Str$(lTotalRecords&)+""
dpText iHandle%, 6,TopMargin, %DDOC_Left,"Date: "+ PBDate(DATE$)+""
dpText iHandle%, 6,TopMargin +.2, %DDOC_Left,"Time: "+ TIME$+""
ROW =TopMargin + LineSpacing * 4
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbGreen, "Arial"
dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "Ameso Hospital" ''& gName
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
dpText IHandle%, 4.25, 1.4, %DDOC_CENTER, "Ogudu, Lagos" ''& gAddress
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
dpText IHandle%, 4.25, 1.7, %DDOC_CENTER, "Debtors Report"
'dpText IHandle%, 6.25, 2.0, %DDOC_CENTER, "Date Range: " & sFrom & " To " & sTo
'dpText IHandle%, .5, 2.3, %DDOC_Left, "Total Debts: " & Format$(dTotalDebts, "#,.00")
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 9, %vbBlack, "Arial"
dpTabText iHandle%, ROW + 0.9, "SNo" + $TAB + "HospitalNo" + $TAB + "Surname" + $TAB + "Othernames" + $TAB + "Balance"
dpFont ihandle%, %DDOC_FONTNORMAL, 8, %vbBlack, "San Serif"
ROW = ROW + LineSpacing * 2
END IF
INCR SNo
DetailLine$ = _
STR$(SNo) + $TAB + _
slFN("HospitalNo") + $TAB + _
slFN("Amount")
dpTabText iHandle%, ROW + 0.7, DetailLine
ROW = ROW + LineSpacing 'increment line counter
LOOP
dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
EndProgram:
END FUNCTION
Hi Fred, glad the solution worked for you. :)
Quote from: Fredrick Ughimi on December 11, 2019, 07:17:56 AM...
Aside: Is it possible to get the:
1 = near ""WITH": syntax error
on one system and not get it on another system? One of my clients had this error, but it worked fine on my system.
Positive error message number is SQLite error msg, not SQLitening. Maybe your client's system has a different (older) version of SQLite .dll installed?
Hello Bern,
>>Maybe your client's system has a different (older) version of SQLite .dll installed?
Yeah that was the problem. Installed the latest version for the client and the error vanished. Thanks.
Hello CJ,
>>Call slSelAry (or slSel once.)
I still will like to make yours work for me.