• Welcome, Guest. Please login.
 
July 14, 2020, 07:32:47 am

News:

Welcome to the SQLitening support forums!


Recent posts

Pages 1 ... 7 8 9 10
81
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 08, 2019, 12:58:34 pm
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.
82
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Bern Ertl - November 08, 2019, 11:11:14 am
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.
83
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 07, 2019, 06:53:29 pm
>>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.
84
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 07, 2019, 06:02:17 pm
>>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.
85
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 07, 2019, 04:32:08 pm
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.

86
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 07, 2019, 04:15:51 pm
I did that. Same error.
87
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Bern Ertl - November 07, 2019, 11:18:34 am
I think you need to add a space character after "Surname" in line 4:

"GROUP BY Surname " & _

88
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 06, 2019, 04:14:31 pm
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"
89
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 06, 2019, 03:22:47 pm
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
90
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 06, 2019, 02:47:13 pm
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)
Pages 1 ... 7 8 9 10