• Welcome, Guest. Please login.
 
July 11, 2020, 09:39:30 am

News:

Welcome to the SQLitening support forums!


Recent posts

Pages 1 ... 6 7 8 9 10
71
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 11, 2019, 12:10:34 pm
I thought the system was under construction, but obviously already done.
Would have to see what the output is supposed to look like.  Nothing like I have seen.
I renamed tables to t1,t2,t3  to try to keep in brain. (services bill, cash/pharmacy bill, payments)
https://sqlite.org/lang_with.html  'need to study WITH

Bern,
I created some data (not good data for testing.)
Renamed amount and surname so they were not duplicates in table1 and table3
Get misuse of aggregate TOTAL().  Remarked Total and it runs.
Learning.  Never even knew about the WITH statement.

Fredrick,
Could you add a few records to have some relational data to test?

I find it a lot easier to use table  T1,T2,T3 while testing.

#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, Name 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("2","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("3","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 Surname1, amount1 + TOTAL(Quantity * SellingPrice - t1.Discount)"
s+="  FROM t1,t3"  'ADDED T3
s+="   LEFT OUTER JOIN t2 ON Surname1 = surname3"
s+="   GROUP BY surname1) "
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="   WHERE TotalBill > Total(Amount3)"
s+="   GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB



#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, Name 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("2","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("3","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 Surname1, amount1 + TOTAL(Quantity * SellingPrice - t1.Discount)"
s+="  FROM t1,t3"  'ADDED T3 to get to work
s+="  LEFT OUTER JOIN t2 ON Surname1 = surname3"
s+="  GROUP BY surname1) "
s+="  SELECT Name,TotalBill FROM Bills"
s+="  LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="  WHERE TotalBill > Total(Amount3)"
s+="  GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
72
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Bern Ertl - November 11, 2019, 12:00:25 pm
You both need to add a space at the end of the first "GROUP BY HospitalNo" string segment.

... GROUP BY HospitalNo Select [b]HospitalNo[[/b] ...
*not* (as you have posted above):

... GROUP BY HospitalNoSelect [b]HospitalNo[[/b] ...
73
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 11, 2019, 03:44:21 am
CJ, Same error near HospitalNo

#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 slOpen ("HospitalProDB.db3", "C")
 ReportDebtors
END FUNCTION

FUNCTION ReportDebtors() AS LONG
 slSel "With Bills(HospitalNo,TotalBill) As (Select HospitalNo, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
          "From tblCashSales " & _
          "Left OUTER JOIN tblBill On tblCashSales.HospitalNo = tblBill.HospitalNo " & _
          "GROUP BY HospitalNo" & _
      "Select [b]HospitalNo[[/b] From Bills " & _
          "Left OUTER JOIN tblPayments On Bills.HospitalNo = tblPayments.HospitalNo " & _
          "WHERE TotalBill > Total( tblPayments.Amount) " & _
          "GROUP BY HospitalNo"
    DO WHILE slGetRow()
      ? slf(1)
    LOOP
END FUNCTION             
74
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 10, 2019, 10:27:16 pm
Thanks CJ. I would give it a shot in the morning.
75
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 10, 2019, 06:55:33 pm
Sorry, I can't figure this one out.  I do in a different way.

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 slOpen "Need data"
 ReportDebtors
END FUNCTION

FUNCTION ReportDebtors() AS LONG
 slSel "With Bills(HospitalNo,TotalBill) As (Select HospitalNo, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
          "From tblCashSales " & _
          "Left OUTER JOIN tblBill On tblCashSales.HospitalNo = tblBill.HospitalNo " & _
          "GROUP BY HospitalNo" & _
      "Select HospitalNo From Bills " & _
          "Left OUTER JOIN tblPayments On Bills.HospitalNo = tblPayments.HospitalNo " & _
          "WHERE TotalBill > Total( tblPayments.Amount) " & _
          "GROUP BY HospitalNo"
    DO WHILE slGetRow()
      ? slf(1)
    LOOP
END FUNCTION
76
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 10, 2019, 06:22:49 pm
CJ, Check attached.

#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 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 PBMAIN () AS LONG

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

     ReportDebtors

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 sFrom AS STRING
      LOCAL sTo AS STRING
      LOCAL dTotalAmount AS DOUBLE
      LOCAL dNetAmount AS DOUBLE

      LineSpacing = .2
      TopMargin   = .5
      PAGESIZE    = 10

      ihandle% = dpStartDoc(0,"Mega-Net HospitalPro - Debtors Report","",%DDOC_INCH, %DDOC_PAPER_A4, %DDOC_LANDSCAPE, %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.W1 L2.8W1 L5.4W2"

      ROW = PAGESIZE                            'force new heading

      slSel "With Bills(HospitalNo,TotalBill) As (Select HospitalNo, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
          "From tblCashSales " & _
          "Left OUTER JOIN tblBill On tblCashSales.HospitalNo = tblBill.HospitalNo " & _
          "GROUP BY HospitalNo" & _
      "Select HospitalNo From Bills " & _
          "Left OUTER JOIN tblPayments On Bills.HospitalNo = tblPayments.HospitalNo " & _
          "WHERE TotalBill > Total( tblPayments.Amount) " & _
          "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_LANDSCAPE, %DDOC_BIN_AUTO
           END IF
           INCR PageNumber
           LOCAL hjpg AS LONG
             hjpg = dpAddGraphic(ihandle%, EXE.PATH$ & "Logo.jpg")
             IF hjpg THEN
                dpDrawGraphic ihandle%, hjpg,5.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%, 10,TopMargin,  %DDOC_Left,"Date: "+ PBDate(DATE$)+""
           dpText iHandle%, 10,TopMargin +.2,  %DDOC_Left,"Time: "+ TIME$+""
           ROW =TopMargin + LineSpacing * 4
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbGreen, "Arial"
           'dpText ihandle, 6.25, 1.1, %DDOC_CENTER, "" & gName
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
           'dpText IHandle%, 6.25, 1.4, %DDOC_CENTER, "" & gAddress
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
           dpText IHandle%, 6.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 Amount: " & FORMAT$(dTotalAmount, "#,.00")
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 9, %vbBlack, "Arial"
           dpTabText iHandle%, ROW + 1.3, "S/No" + $TAB + "HNO"  + $TAB + "Surname" + $TAB + "Othernames"
           dpFont ihandle%, %DDOC_FONTNORMAL, 8, %vbBlack, "San Serif"
           ROW =  ROW + LineSpacing * 2
        END IF

        DetailLine$ =  _
          STR$(SNo) + $TAB + _
          slFN("HospitalNo") + $TAB + _
          slFN("Surname") + $TAB + _
          slFN("Othernames")

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

      LOOP

      dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
    EndProgram:
 END FUNCTION
77
You've got Questions? We've got Answers! / Re: Debtors List
Last post by cj - November 10, 2019, 05:04:21 pm
Post something with data and we can test it.
78
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 10, 2019, 04:46:46 pm
Hello Bern,

Thank you.

The table name are in the correct order in my version. I used the HospitalNo field in place of the Surname because it is common to all the three tables. I get an error near the bolded HospitalNo. It was the same error with your codes (Name).

Your Code: Error in the bolded "Name"
 
slSel "With Bills(Name,TotalBill) As (Select Surname, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
          "From tblBill " & _
          "Left OUTER JOIN tblBill On tblCashSales.Surname = tblBill.Surname " & _
          "GROUP BY Surname" & _
      "Select [b]Name[/b] From Bills " & _
          "Left OUTER JOIN tblPayments On Bills.Name = tblPayments.Surname " & _
          "WHERE TotalBill > Total( tblPayments.Amount) " & _
          "GROUP BY Surname"                 

My version with the corect tables in place. Error near the bolded HospitalNo
slSel "With Bills(HospitalNo,TotalBill) As (Select HospitalNo, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
          "From tblCashSales " & _
          "Left OUTER JOIN tblBill On tblCashSales.HospitalNo = tblBill.HospitalNo " & _
          "GROUP BY HospitalNo" & _
      "Select [b]HospitalNo[/b] From Bills " & _
          "Left OUTER JOIN tblPayments On Bills.HospitalNo = tblPayments.HospitalNo " & _
          "WHERE TotalBill > Total( tblPayments.Amount) " & _
          "GROUP BY HospitalNo" 

This method would work best for me if I could it to run.

Best regards.
79
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 08, 2019, 12:58:34 pm
Hello Bern,

The real names of the tables are not the ones I mentioned in my OP. I changed them in my OP just to depict what they do, so others can easily understand.

tblPharmacyBill is tblCashSales (Real Name)

tblPayments is tblPayments (Same Name)

tblServicesBill is tblBill (Real Name)

It still would not compile.

Thanks.
80
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Bern Ertl - November 08, 2019, 11:11:14 am
Quote from: Fredrick Ughimi on November 07, 2019, 06:02:17 pm...
The tblPayments is used to store patients payments against the bills on services (tblBill) and pharmacy (tblCashSales). Installment payments are allowed to clear up the bills.

Looks like you changed some of the table names from what you posted in the OP.  Please make sure that all the table names in the code are correct: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"

*** => should be the same table - whichever is the correct table name.
Pages 1 ... 6 7 8 9 10