• Welcome, Guest. Please login.
 
January 26, 2021, 07:42:03 AM

News:

Welcome to the SQLitening support forums!


Recent posts

Pages 1 ... 8 9 10
91
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.
92
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 " & _

93
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"
94
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
95
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)
96
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 06, 2019, 01:19:33 PM
Wow! Thank you CJ and Been.

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

Regards.
97
Quote from: Fredrick Ughimi on September 05, 2019, 03:57:53 PMHello,

I am stump trying to figure out how to automatically 'trigger' this sequence of SQL startements. I wonder if Trigger is the best condidate.
...
But triggers are only triggered when an INSERT, DELETE or UPDATE occurs. I want the statements triggered when this conditions are met:

If (sDepartureDate < Date$) Or (sDepartureDate = Date$ And Time$ > "12") Then
....
End If

Any help would be appreciated.

TRIGGERs are great.  You will likely need 3 triggers to cover all possible data changes that might impact the condition you want monitored:  INSERT and DELETE on tblRegistration and UPDATE on tblRegistration.DepartureDate
98
Quote from: cj on July 21, 2019, 11:15:22 AM2. Is this to add a time stamp to
  Every insert (unlikely)
  Create a table logging all entries?
  Create a flat file text log on disk?

3. This is for the server, right?

My original code is for generating time stamps on the server.  The SQLite custom function can be called/used from within SQL statements.  This allows for transaction processing using TRIGGERs.  For example, when modifying data in a table (insert, update, delete), a trigger could log the change made and server time in another (transaction) table.  The use of a TRIGGER with the custom function ensures that the logging is atomic with the change.
99
You've got Questions? We've got Answers! / Re: SELECT strftime
Last post by Bern Ertl - November 06, 2019, 12:06:48 PM
Quote from: Fim on October 16, 2019, 01:02:33 PM... how to get it from the server?

I posted some code years ago for creating and using a server side time stamp.  It uses Windows FILETIME data, but it's easily converted to whatever format you need:

https://sqlitening.planetsquires.com/index.php?topic=3539.0
100
Quote from: Fredrick Ughimi on July 20, 2019, 08:13:27 PM...
What is the difference SQLiteningProcsA and SQLiteningProcsB?
...

It doesn't matter. 

SQLiteningProcsA
SQLiteningProcsB
SQLiteningProcsC
SQLiteningProcsD
SQLiteningProcsE
SQLiteningProcsF
...
SQLiteningProcsZ


You can use any of them.  SQLitening allows you to organize your server side code libraries into separate files with this naming convention, but there is no difference as to which you use.  It's just for you to organize your code.

I mainly use SQLiteningProcsT and SQLiteningProcsP for my server code.  The "T" library contains server code that is loaded, used and unloaded (ie. "Temporary").  The "P" library contains code that is loaded and stays loaded until the app closes (ie. "Permanent").
Pages 1 ... 8 9 10