• Welcome, Guest. Please login.
 
November 21, 2019, 03:44:06 pm

News:

Welcome to the SQLitening support forums!


Debtors List

Started by Fredrick Ughimi, November 05, 2019, 05:19:58 pm

Previous topic - Next topic

Fredrick Ughimi

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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Bern Ertl

November 06, 2019, 11:50:17 am #1 Last Edit: November 06, 2019, 11:52:59 am by Bern Ertl
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

Fredrick Ughimi

Wow! Thank you CJ and Been.

I would try both suggested solutions and get back to you.

Regards.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

November 06, 2019, 02:47:13 pm #3 Last Edit: November 06, 2019, 03:30:20 pm by cj
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)

cj

November 06, 2019, 03:22:47 pm #4 Last Edit: November 06, 2019, 03:37:26 pm by cj
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

Fredrick Ughimi

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"
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Bern Ertl

I think you need to add a space character after "Surname" in line 4:

"GROUP BY Surname " & _


Fredrick Ughimi

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

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.

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

November 07, 2019, 04:33:12 pm #9 Last Edit: November 07, 2019, 05:12:54 pm by cj
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

Fredrick Ughimi

>>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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

>>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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

November 07, 2019, 07:10:40 pm #12 Last Edit: November 07, 2019, 07:55:27 pm by cj
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

Bern Ertl

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.

Fredrick Ughimi

November 08, 2019, 12:58:34 pm #14 Last Edit: November 09, 2019, 03:31:41 am by Fredrick Ughimi
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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet