• Welcome, Guest. Please login.
 
April 01, 2020, 12:15:14 am

News:

Welcome to the SQLitening support forums!


Recent posts

Pages 1 2 3 4 5 ... 10
21
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Bern Ertl - December 10, 2019, 04:26:00 pm
Quote from: Fredrick Ughimi on December 10, 2019, 01:56:25 pm...
Yes, there is a master table I called tblBioData which contains the HospitalNo, Surname, Othernames, etc.
...

Then this should work (adjust table names to match your code):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 tblBioData"
s+="   LEFT OUTER JOIN SumT1 ON tblBioData.Surname = ST1Surname"
s+="   LEFT OUTER JOIN SumT2 ON tblBioData.Surname = ST2Surname"
s+="   LEFT OUTER JOIN SumT3 ON tblBioData.Surname = ST3Surname"
s+="   WHERE ST1Total + ST2Total > ST3Total"
22
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - December 10, 2019, 02:04:48 pm
Hello CJ,

>>The dummy columns t1,t2,t3 are required to get the correct results.  Please test!

I will test it as soon as I am with my computer. Thanks a lot.
23
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - December 10, 2019, 01:56:25 pm
Hello Bern,

>>That's because I used INNER JOINs. You can use LEFT OUTER JOINS if you want results from partial matches, but even so, the table referenced after FROM will control the results set, so the results only return for records in that table. 

Thank you for your explanations.


>>Like I said previously, if you have a fourth table that contains a master list of the Surnames/HopsitalNos, you can use that as the FROM reference and LEFT OUTER JOIN the 3 "SUM" tables from the WITH clause and get a full result set.


Yes, there is a master table I called tblBioData which contains the HospitalNo, Surname, Othernames, etc.

I will follow your instructions and see how it goes. Thanks a lot.


24
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Bern Ertl - December 10, 2019, 11:54:23 am
Quote from: Fredrick Ughimi on December 10, 2019, 10:14:35 am...
>>Bern routine results: 1 payment record must exist to return results for a client

I also noticed that all three tables needs to have data for a client to return results for that client.

That's because I used INNER JOINs. You can use LEFT OUTER JOINS if you want results from partial matches, but even so, the table referenced after FROM will control the results set, so the results only return for records in that table. 

Like I said previously, if you have a fourth table that contains a master list of the Surnames/HopsitalNos, you can use that as the FROM reference and LEFT OUTER JOIN the 3 "SUM" tables from the WITH clause and get a full result set.
25
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - December 10, 2019, 10:14:35 am
Hello Cj,

I tried implementing your version today:

#COMPILE EXE
#DIM ALL

'Both 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.
'
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
   LOCAL sData(),sBind AS STRING, x AS LONG

   slopen "HospitalProDB.db3","C"
   slexe  "drop table if exists TempBalances;create table TempBalances(HospitalNo unique,Amount)"

 'combine tables and payments
 slselary " select HospitalNo,SUM(Amount),'tblBill' from tblBill group by HospitalNo"  +_     'charges table1
          " union "                                      +_
          " select HospitalNo,SUM(SellingPrice * Quantity - Discount) as Amount,'tblCashSales' from tblCashSales group by HospitalNo"  +_     'charges table2
          " union "                                      +_
          " select HospitalNo,SUM(Amount),'tblPayments' from tblPayments group by HospitalNo",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(HospitalNo) DO update SET Amount=Amount+" + 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

I got incomplete input error.

>>Bern routine results: 1 payment record must exist to return results for a client

I also noticed that all three tables needs to have data for a client to return results for that client.
26
You've got Questions? We've got Answers! / Re: Would This Work?
Last post by Fredrick Ughimi - December 03, 2019, 11:05:56 am
>>Very interesting read:
https://proprivacy.com/vpn/comparison/nigeria-vpn

Thanks for the link. Interesting read.
27
You've got Questions? We've got Answers! / Re: Would This Work?
Last post by cj - December 03, 2019, 12:52:55 am
Very interesting read:
https://proprivacy.com/vpn/comparison/nigeria-vpn

I have been successfully using SQLitening over a VPN between machines that share the exact same dedicated VPN server using the same user name and password.  This might be the best way to go if this is allowed.
I paid nothing extra and didn't need any dedicated IP address (other then share same exact server.)

I will finally have time to work on encrypting SQLitening on 12/3.
28
You've got Questions? We've got Answers! / Re: Would This Work?
Last post by cj - December 02, 2019, 11:55:06 am
https://help.logmein.com/pkb_Home?q=share%20a%20port&l=en_US&c=products%3Almihamachi2&atype=FAQ__kav

It looks like you share a folder and use Windows mapping.
I only connected 2 machines and used chat.
I didn't use SQLite with it.
There are links to PDF files at the bottom of their home page.
Many use it so there must be better docs on the net, don't know where.
29
You've got Questions? We've got Answers! / Re: Would This Work?
Last post by Fredrick Ughimi - December 02, 2019, 11:40:01 am
Hello CJ,

>>I got Hamachi to connect, but didn't go any further.

How did you do it?
30
You've got Questions? We've got Answers! / Re: Would This Work?
Last post by cj - December 01, 2019, 03:40:23 pm
Windows 10 has a built-in VPN server, but I couldn't connect.
Some on the internet say there is a problem introduced in a Windows update.
https://pureinfotech.com/setup-vpn-server-windows-10/

I got Hamachi to connect, but didn't go any further.

I'll go back to encrypting the TCP data being transmit to the server.
Pages 1 2 3 4 5 ... 10