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;

"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)

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)
>