• Welcome, Guest. Please login.
 
January 27, 2020, 05:31:04 pm

News:

Welcome to the SQLitening support forums!


Debtors List

Started by Fredrick Ughimi, November 05, 2019, 05:19:58 pm

Previous topic - Next topic

cj

https://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.




Fredrick Ughimi

Sqlite3 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.

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

cj

Need the relationships and what a detail line or the column heading should be.
Hosital Number  Client Number Invoice Number ....

Bern Ertl

OK.  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.

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

cj

balances.png

#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

cj

November 15, 2019, 11:54:33 am #35 Last Edit: November 15, 2019, 04:15:19 pm by cj
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 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

Fredrick Ughimi

November 17, 2019, 02:36:15 pm #36 Last Edit: November 17, 2019, 02:39:19 pm by Fredrick Ughimi
Hello 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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

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

Bern Ertl

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.

Fredrick Ughimi

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.


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

Fredrick Ughimi

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

Bern Ertl

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"

Fredrick Ughimi

Hello Bern,

>>Then this should work (adjust table names to match your code):

This worked. I am going to test further in the days to come.

Thanks a million.

s=  "WITH"
        s+="   TempT1(TT1HospitalNo,Surname, Othernames,TT1Total) AS (SELECT HospitalNo, Surname, Othernames, Amount - Discount"
        s+="     FROM tblBill),"
        s+="   TempT2(TT2HospitalNo,Name,TT2Total) AS (SELECT tblCashSales.HospitalNo, Name, Quantity * SellingPrice - Discount"
        s+="     FROM tblCashSales),"
        s+="   SumT1( ST1HospitalNo, ST1Total) AS (SELECT TT1HospitalNo, TOTAL( TT1Total)"
        s+="     FROM TempT1 GROUP BY TT1HospitalNo),"
        s+="   SumT2( ST2HospitalNo, ST2Total) AS (SELECT TT2HospitalNo, TOTAL( TT2Total)"
        s+="     FROM TempT2 GROUP BY TT2HospitalNo),"
        s+="   SumT3(ST3HospitalNo, Surname, Othernames, ST3Total) AS (SELECT tblPayments.HospitalNo, Surname, Othernames, TOTAL(Amount)"
        s+="     FROM tblPayments GROUP BY tblPayments.HospitalNo)"
        s+=" SELECT tblBioData.HospitalNo, tblBioData.Surname, tblBioData.Othernames, ST1Total + ST2Total - ST3Total"
        s+="   FROM tblBioData"
        s+="   LEFT OUTER JOIN SumT1 ON tblBioData.HospitalNo = ST1HospitalNo"
        s+="   LEFT OUTER JOIN SumT2 ON tblBioData.HospitalNo = ST2HospitalNo"
        s+="   LEFT OUTER JOIN SumT3 ON tblBioData.HospitalNo = ST3HospitalNo"
        s+="   WHERE ST1Total + ST2Total > ST3Total"           

Aside: Is it possible to get the:

1 = near ""WITH": syntax error

on one system and not get it on another system? One of my clients had this error, but it worked fine on my system.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

December 11, 2019, 07:56:43 am #43 Last Edit: December 11, 2019, 08:36:22 am by Fredrick Ughimi
Hi CJ,

I tried your code directly against my database. It compiled and ran without showing any result.

#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG

 slopen "HospitalProDB.db3","C"

 LOCAL sData() AS STRING

 slselary " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill group by HospitalNo" +_
          " UNION" +_
          " select HospitalNo,SUM(SellingPrice*Quantity-Discount),'tblCashSales' from tblCashSales group by HospitalNo" +_
          " UNION" +_
          " select HospitalNo,SUM(Amount),  'tblPayments' from tblPayments group by HospitalNo",sData()

 slselary " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill  group by HospitalNo     UNION "  +_
          " select HospitalNo,SUM(SellingPrice * Quantity  - Discount),'tblCashSales' from tblCashSales  group by HospitalNo UNION "  +_
          " select HospitalNo,SUM(Amount),  'tblPayments' from tblPayments group by HospitalNo",sData()

 'without temp table
 LOCAL x,linecounter AS LONG
 DIM hospital(999) AS LONG
 DIM sArray(999)   AS STRING
 FOR x = 1 TO UBOUND(sdata,2)
  hospital(VAL(sdata(1,x))) += VAL(sdata(2,x)) 'combine amounts in column2
 NEXT
 sArray(0) = "Hospital  Amount"  'optional heading
 FOR x = 1 TO UBOUND(hospital)   'hospital 1 to ?
  IF hospital(x) THEN            'if hospital <> 0
   INCR linecounter
   sArray(linecounter) = USING$("#&##.##",x,$TAB,hospital(x)*.01)
  'sArray(linecounter) = FORMAT$(x) + $TAB + USINg$("##.##",hospital(x)*.01)
  END IF
 NEXT
 REDIM PRESERVE sArray(linecounter)
 ? JOIN$(sArray(),$CR),,"Balances"

END FUNCTION


I tried again using DDOC this time and discovered I could retrieve the balances.

#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"
#INCLUDE "ddoc_p32.inc"

'DDOC
'  Needed Color Constants (Vb compatible)
'---------------------------------------------------------------------------
%vbBlack = &H0&
%vbRed = &HFF&
%vbGreen = &HFF00&
%vbYellow = &HFFFF&
%vbBlue = &HFF0000
%vbMagenta = &HFF00FF
%vbCyan = &HFFFF00
%vbWhite = &HFFFFFF
%vbGrey = &HC0C0C0
%LIGHT_SHADE = &HE0E0E0

FUNCTION PBMAIN () AS LONG

   slOpen ("HospitalProDB.db3", "C")

   ReportDebtors

END FUNCTION

FUNCTION PBDate(sDate AS STRING) AS STRING
     'MM-dd-yyyy to dd-MM-yyyy

     FUNCTION = MID$(sDate,4,2) & "-" & MID$(sDate,1,2) & "-" & MID$(sDate,7,4)

END FUNCTION

FUNCTION NigeriaDate(sDate AS STRING) AS STRING
     'yyyy-MM-dd  to dd-MM-yyyy
     FUNCTION = MID$(sDate,9,2) & "-" & MID$(sDate,6,2) & "-" & MID$(sDate,1,4)

END FUNCTION

FUNCTION SQLiteDate(sDate AS STRING) AS STRING
     'From dd-MM-yyyy To yyyy-MM-dd

     FUNCTION = MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2)

END FUNCTION

FUNCTION ReportDebtors() AS LONG
      LOCAL dbHandle&, LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
      LOCAL detailline AS ASCIIZ * 200
      LOCAL SNo AS LONG
      LOCAL lTotalRecords&
      LOCAL dTotalAmount AS DOUBLE
      LOCAL dNetAmount AS DOUBLE
      LOCAL s AS STRING
      LOCAL dTotalDebts, dTotalBill, dTotalPayment AS DOUBLE

      LineSpacing = .2
      TopMargin   = .5
      PAGESIZE    = 10

      ihandle% = dpStartDoc(0,"Mega-Net HospitalPro - Debtors Report","",%DDOC_INCH, %DDOC_PAPER_A4, %DDOC_PORTRAIT, %DDOC_SYSTEM_DEFAULT, %DDOC_BIN_AUTO OR %DDOC_ALLOWSMTP OR %DDOC_ALLOWSAVE OR %DDOC_ZOOMFIT)
      IF ihandle < 1 THEN
           MSGBOX "Could not StartDoc. Error number" + STR$(Ihandle)
           GOTO EndProgram
      END IF

      dpSetTabs iHandle, "L.5W1 L1.0W1 L2.5W3 L5.5W1 L6.5W1 L7.5W1"

      ROW = PAGESIZE                            'force new heading

    slSEL " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill group by HospitalNo" +_
          " UNION" +_
          " select HospitalNo,SUM(SellingPrice*Quantity-Discount),'tblCashSales' from tblCashSales group by HospitalNo" +_
          " UNION" +_
          " select HospitalNo,SUM(Amount),  'tblPayments' from tblPayments group by HospitalNo"

   slSEL " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill  group by HospitalNo UNION "  +_
          " select HospitalNo,SUM(SellingPrice * Quantity  - Discount),'tblCashSales' from tblCashSales group by HospitalNo UNION "  +_
          " select HospitalNo,SUM(Amount),  'tblPayments' from tblPayments group by HospitalNo"


      DO WHILE slGetRow()

        IF ROW => PAGESIZE THEN
           IF PageNumber THEN      'If first page don't need a new page
              dpNewPage iHandle%,  %ddoc_PAPER_A4, %DDOC_PORTRAIT, %DDOC_BIN_AUTO
           END IF
           INCR PageNumber
           LOCAL hjpg AS LONG
             ''hjpg = dpAddGraphic(ihandle%, VD_App.Path & "Logo.jpg")
             IF hjpg THEN
                dpDrawGraphic ihandle%, hjpg,3.5,.2,2,1
             END IF

           dpText iHandle%, .5,TopMargin,  %DDOC_Left,"Page: "+ STR$(PageNumber)+""
           'dpText iHandle%, .5,TopMargin + .2,  %DDOC_Left,"Total Records: "+ Str$(lTotalRecords&)+""
           dpText iHandle%, 6,TopMargin,  %DDOC_Left,"Date: "+ PBDate(DATE$)+""
           dpText iHandle%, 6,TopMargin +.2,  %DDOC_Left,"Time: "+ TIME$+""
           ROW =TopMargin + LineSpacing * 4
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbGreen, "Arial"
           dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "Ameso Hospital" ''& gName
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
           dpText IHandle%, 4.25, 1.4, %DDOC_CENTER, "Ogudu, Lagos" ''& gAddress
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
           dpText IHandle%, 4.25, 1.7, %DDOC_CENTER, "Debtors Report"
           'dpText IHandle%, 6.25, 2.0, %DDOC_CENTER, "Date Range:  " & sFrom  & " To " & sTo
           'dpText IHandle%, .5, 2.3, %DDOC_Left, "Total Debts: " & Format$(dTotalDebts, "#,.00")
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 9, %vbBlack, "Arial"
           dpTabText iHandle%, ROW + 0.9, "SNo" + $TAB + "HospitalNo" + $TAB + "Surname" + $TAB + "Othernames" + $TAB + "Balance"
           dpFont ihandle%, %DDOC_FONTNORMAL, 8, %vbBlack, "San Serif"
           ROW =  ROW + LineSpacing * 2
        END IF

        INCR SNo
        DetailLine$ =  _
          STR$(SNo) + $TAB + _
          slFN("HospitalNo") + $TAB + _
          slFN("Amount")

          dpTabText iHandle%, ROW + 0.7, DetailLine
          ROW = ROW + LineSpacing                    'increment line counter

      LOOP

      dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
    EndProgram:
    END FUNCTION
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Bern Ertl

Hi Fred, glad the solution worked for you.  :)

Quote from: Fredrick Ughimi on December 11, 2019, 07:17:56 am...
Aside: Is it possible to get the:

1 = near ""WITH": syntax error

on one system and not get it on another system? One of my clients had this error, but it worked fine on my system.

Positive error message number is SQLite error msg, not SQLitening.  Maybe your client's system has a different (older) version of SQLite .dll installed?