- Welcome, Guest. Please login.
1
You've got Questions? We've got Answers! / Would This Work?
Last post by Fredrick Ughimi - November 21, 2019, 06:35:12 amHello,
Just wondering if SQLitening can be used on a LAN and at the same time over the Internet. I imagine a scenario where users (staff) use the SQLitening Application on a LAN while the Boss accesses or uses the application over the Internet from a remote location.
Any ideas? Thanks.
Just wondering if SQLitening can be used on a LAN and at the same time over the Internet. I imagine a scenario where users (staff) use the SQLitening Application on a LAN while the Boss accesses or uses the application over the Internet from a remote location.
Any ideas? Thanks.
2
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 17, 2019, 02:36:15 pmHello 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.
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.
3
You've got Questions? We've got Answers! / Balance Reports Compared
Last post by cj - November 15, 2019, 11:54:33 amBoth 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.
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
4
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 15, 2019, 02:04:58 am#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
5
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Bern Ertl - November 14, 2019, 12:56:02 pmOK. 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.
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.
Code 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, 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.
6
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 13, 2019, 06:30:04 pmNeed the relationships and what a detail line or the column heading should be.
Hosital Number Client Number Invoice Number ....
Hosital Number Client Number Invoice Number ....
7
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 13, 2019, 10:11:11 amSqlite3 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.
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.
8
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 13, 2019, 09:46:36 amhttps://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.
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.
9
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 13, 2019, 09:37:06 amHello,
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.
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.
10
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 12, 2019, 12:59:12 pmChanged Amount to Amount1
Misuse of aggregate Total()
Misuse of aggregate Total()
Code 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, 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