• Welcome, Guest. Please login.
 
August 07, 2020, 12:36:31 pm

News:

Welcome to the SQLitening support forums!


Recent posts

Pages 1 ... 5 6 7 8 9 10
61
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 13, 2019, 06:30:04 pm
Need the relationships and what a detail line or the column heading should be.
Hosital Number  Client Number Invoice Number ....
62
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 13, 2019, 10:11:11 am
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.

63
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 13, 2019, 09:46:36 am
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.



64
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 13, 2019, 09:37:06 am
Hello,

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.
65
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 12, 2019, 12:59:12 pm
Changed Amount to Amount1
Misuse of aggregate Total()

#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
                                 
66
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Bern Ertl - November 12, 2019, 12:50:25 pm
Ooops  (red face).  I misplaced the end parenthesis on the WITH statements....

WITH
   TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount - Discount
     FROM T1),
   TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Quantity * SellingPrice - Discount
     FROM T2)
...
67
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 12, 2019, 12:46:51 pm
Error near FROM  (image at bottom)
Hope Fredrick needs this  :o
I'm going back to learning mode (about WITH)
I don't match payments to invoices so this is all new to me.

#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, Amount - 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
68
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Bern Ertl - November 12, 2019, 12:28:28 pm
I wasn't sure if TOTAL(Quantity * SellingPrice - t2.Discount) would work as I was expecting.  If that is what SQLite is choking on, the statement will need to be reworked a bit.  Something like this:

WITH
   TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount - Discount)
     FROM T1,
   TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Quantity * SellingPrice - Discount)
     FROM T2
 SELECT Surname3, Total( TT1Total) + Total( TT2Total) - Total(Amount3)
   FROM T3
   LEFT OUTER JOIN TempT1 ON Surname3 = TT1Surname
   LEFT OUTER JOIN TempT2 ON Surname3 = TT2Surname
   WHERE Total( TT1Total) + Total( TT2Total) > Total(Amount3)
   GROUP BY surname3

I noticed that OP's table definitions included a discount field in T1, but it wasn't included in the desired logic.  Above code assumes that was an oversight and any discounts in T1 should be considered in the calculations.
69
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 12, 2019, 11:01:21 am
Almost accepted (incorrect use of aggregate Total()

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3

s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, Total(amount1) + TOTAL(Quantity * SellingPrice - t2.Discount)"
s+="  FROM t1,t2"
'       LEFT OUTER JOIN t2 ON t1.Surname1 = t2.surname2"
s+= "   WHERE surname1 = surname2"
s+="   GROUP BY surname2)"
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="   WHERE TotalBill > Total(Amount3)"
s+="   GROUP BY surname3

or using OUTER JOIN still returns incorrect use of Total()

SUB Bern
s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, Total(amount1) + TOTAL(Quantity * SellingPrice - t2.Discount)"
s+="  FROM t1"
s+="   LEFT OUTER JOIN t2 ON t1.Surname1 = t2.surname2"
s+="   GROUP BY surname2)"
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="   WHERE TotalBill > Total(Amount3)"
s+="   GROUP BY surname3"
70
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 12, 2019, 10:53:31 am
I missed that incorrect naming SurnameName2 should be "Surname2".
It works now except incorrect use of aggregate Total().

#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
 'table1 and table3 surname1,surname3 amount1,amount3
s = " WITH Bills(Name,TotalBill) AS (SELECT t1.Surname1, t1.amount1 + TOTAL(Quantity * SellingPrice - t2.Discount)"
s+="  FROM t2,t1"
's+="   LEFT OUTER JOIN t2 ON t1.Surname1 = t2.surname2"
s+= "   WHERE surname1 = surname2"
s+="   GROUP BY surname2 )"
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = t3.surname3"
s+="   WHERE TotalBill > Total(t3.Amount3)"
s+="   GROUP BY t3.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
Pages 1 ... 5 6 7 8 9 10