Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
SQL Join Help Please |
Wed, Dec 20 2006 2:24 PM | Permanent Link |
"Sean McDermott" | I have a query that joins an invoice table to a payments table and pushes
the data into a temporary file. What I am trying to do is select all invoices for a user-selected date range and ONLY the payments for these invoices ALSO fall within the user-selected date range. For example, I may want to look at all invoices for December and the associated payments. Currently, I get all invoices for December and ALL payments for these invoices regardless if they were paid in December or some other month. I am trying to produce a balance sheet type report that would ignore payments made after the end date of the query. What I have so far is shown below. Any and all help appreciated, I am missing something simple no doubt. Cheers, Sean PS. the invoice and clientid fields are indexed... qryTmpInvoices.Close; qryTmpInvoices.SQL.Clear; qryTmpInvoices.SQL.Add('SELECT invoice, clientid, billedto, lastname, invoicedate, paiddate, billedamount, invoicetype, invoicepaidinfull, sum(p.paidamount) as TotalPaid INTO "\MEMORY\tmpInvoices" from invoices i'); qryTmpInvoices.SQL.Add('left outer join payments p on (p.invoice = i.invoice) where (InvoiceDate >=:Par1 and InvoiceDate <=:Par2) group by invoice order by invoice'); qryTmpInvoices.ParambyName('Par1').AsDateTime := frmQueryCustDlg.FromDate; qryTmpInvoices.ParambyName('Par2').AsDateTime := frmQueryCustDlg.ToDate; qryTmpInvoices.Open; |
Wed, Dec 20 2006 5:24 PM | Permanent Link |
"Robert" | "Sean McDermott" <Sean@HorizonCanada.com> wrote in message news:894A2D45-3648-4458-A843-6BEA06715B41@news.elevatesoft.com... >I have a query that joins an invoice table to a payments table and pushes >the data into a temporary file. What I am trying to do is select all >invoices for a user-selected date range and ONLY the payments for these >invoices ALSO fall within the user-selected date range. For example, I may >want to look at all invoices for December and the associated payments. >Currently, I get all invoices for December and ALL payments for these >invoices regardless if they were paid in December or some other month. I am >trying to produce a balance sheet type report that would ignore payments >made after the end date of the query. What I have so far is shown below. >Any and all help appreciated, I am missing something simple no doubt. >Cheers, Sean > > PS. the invoice and clientid fields are indexed... > > qryTmpInvoices.Close; > qryTmpInvoices.SQL.Clear; > qryTmpInvoices.SQL.Add('SELECT invoice, clientid, billedto, > lastname, invoicedate, paiddate, billedamount, invoicetype, > invoicepaidinfull, sum(p.paidamount) as TotalPaid INTO > "\MEMORY\tmpInvoices" from invoices i'); > qryTmpInvoices.SQL.Add('left outer join payments p on (p.invoice = > i.invoice) where (InvoiceDate >=:Par1 and InvoiceDate <=:Par2) group by > invoice order by invoice'); > qryTmpInvoices.ParambyName('Par1').AsDateTime := > frmQueryCustDlg.FromDate; > qryTmpInvoices.ParambyName('Par2').AsDateTime := > frmQueryCustDlg.ToDate; > qryTmpInvoices.Open; > > Several problems: 1) you need to select payments based on date range, otherwise as you noted you'll get all payments and 2) You are selecting fields that are not aggregate fields or part of the group by clause. While this is no GUARANTEE of problems, it is bad code (IMO) and should be avoided. You can run the scripts below as two separate scripts. The reason being because DBISAM will only resolve parameters for the first select in the script. If you want to run the whole thing in a single script, instead of using parameters do a SQL.Text string replace changing the parameters for SQL formatted dates '2006-12-20'. SELECT SUM(PaidAmount) TotalPaid, Invoice TPInvoice into memory\temp1 from Payments Where PaymentDate between :Par1 and :Par2 group by Invoice; create index ByInvoice on memory\temp1 (TPInvoice); SELECT Invoice, ClientID, etc, TotalPaid from Invoices LEFT OUTER JOIN Memory\Temp1 on invoice = TPInvoice where (InvoiceDate >=:Par1 and InvoiceDate <=:Par2) |
Thu, Dec 21 2006 8:53 AM | Permanent Link |
"Sean McDermott" | Thanks Robert, I'll try this, thanks, Sean
"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message news:0C223ED2-A939-4B8C-996A-0D3630524C67@news.elevatesoft.com... > > "Sean McDermott" <Sean@HorizonCanada.com> wrote in message > news:894A2D45-3648-4458-A843-6BEA06715B41@news.elevatesoft.com... >>I have a query that joins an invoice table to a payments table and pushes >>the data into a temporary file. What I am trying to do is select all >>invoices for a user-selected date range and ONLY the payments for these >>invoices ALSO fall within the user-selected date range. For example, I may >>want to look at all invoices for December and the associated payments. >>Currently, I get all invoices for December and ALL payments for these >>invoices regardless if they were paid in December or some other month. I >>am trying to produce a balance sheet type report that would ignore >>payments made after the end date of the query. What I have so far is shown >>below. Any and all help appreciated, I am missing something simple no >>doubt. Cheers, Sean >> >> PS. the invoice and clientid fields are indexed... >> >> qryTmpInvoices.Close; >> qryTmpInvoices.SQL.Clear; >> qryTmpInvoices.SQL.Add('SELECT invoice, clientid, billedto, >> lastname, invoicedate, paiddate, billedamount, invoicetype, >> invoicepaidinfull, sum(p.paidamount) as TotalPaid INTO >> "\MEMORY\tmpInvoices" from invoices i'); >> qryTmpInvoices.SQL.Add('left outer join payments p on (p.invoice = >> i.invoice) where (InvoiceDate >=:Par1 and InvoiceDate <=:Par2) group by >> invoice order by invoice'); >> qryTmpInvoices.ParambyName('Par1').AsDateTime := >> frmQueryCustDlg.FromDate; >> qryTmpInvoices.ParambyName('Par2').AsDateTime := >> frmQueryCustDlg.ToDate; >> qryTmpInvoices.Open; >> >> > > Several problems: 1) you need to select payments based on date range, > otherwise as you noted you'll get all payments and 2) You are selecting > fields that are not aggregate fields or part of the group by clause. While > this is no GUARANTEE of problems, it is bad code (IMO) and should be > avoided. > > You can run the scripts below as two separate scripts. The reason being > because DBISAM will only resolve parameters for the first select in the > script. If you want to run the whole thing in a single script, instead of > using parameters do a SQL.Text string replace changing the parameters for > SQL formatted dates '2006-12-20'. > > SELECT SUM(PaidAmount) TotalPaid, Invoice TPInvoice into memory\temp1 from > Payments Where PaymentDate between :Par1 and :Par2 group by Invoice; > create index ByInvoice on memory\temp1 (TPInvoice); > > SELECT Invoice, ClientID, etc, TotalPaid from Invoices > LEFT OUTER JOIN Memory\Temp1 on invoice = TPInvoice > where (InvoiceDate >=:Par1 and InvoiceDate <=:Par2) > |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |