• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

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

#1
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

#3
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

#4
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

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

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

#12
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

Fredrick Ughimi

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

cj

#14
Post something with data and we can test it.