T Well I'd index those fields as they are a primary selection criteria (This is an oft overlooked part of SQL DB Management because they will do virtually anything but the worst case as a linear search) also as your datasize increases the date columns will define your working 'window'
All these 'Sums' do you want them grouped by MachineID/ Ref N ----- Original Message ----- From: "Tracey Maule" <[EMAIL PROTECTED]> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> Sent: Thursday, July 31, 2003 10:51 AM Subject: Re: [DUG]: Interbase SQL > > > > is JOBLINE.Job Date ENTRY.EntryDate Indexed? > > Also you want all 'all entries' and 'all Job Lines'? > > > no > and no > > The data i actually need is: > sum(entry.quantity) as manhours > sum(entry.endquantity - entry.startquantity) as machinehours > sum(entry.fuel) as fuel > sum(jobline.joblinecost) as jobcost > machine.ref > machine.machineid > > where companyid = companyid > and dates in Daterange > > > > > SELECT > > 'JobLine', JOBLINE.JobDate, JOBLINE.JobLineID, Job.MachineID > > FROM > > JOBLINE JOIN > > JOB ON JOBLINE.JobID = JOB.JobID > > WHERE > > JOBLINE.JobDate BETWEEN Date1 AND Date2 > > UNION > > SELECT > > 'Entry', ENTRY.EntryDate, ENTRY.EntryID, ENTRY.MachineID, > > FROM > > ENTRY > > WHERE > > ENTRY.EntryDate BETWEEN Date1 AND Date2 > > > > ----- Original Message ----- > > From: "Tracey Maule" <[EMAIL PROTECTED]> > > To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> > > Sent: Thursday, July 31, 2003 9:37 AM > > Subject: [DUG]: Interbase SQL > > > > > > The usual topic im afraid... > > I have the following tables: > > > > MACHINE > > machineid > > > > ENTRY > > EntryID > > MachineID > > EntryDate > > Quantity > > StartQuantity > > EndQuantity > > > > JOB > > JobID > > MachineID > > > > JOBLINE > > JoblineID > > JobID > > JobDate > > JobLineCost > > > > I need to select all entries between 2 dates, and all joblines between 2 > > dates, for every machine where there is an entry in one or the other. > > > > rather than selecting all machines, then doing individual selects from > each > > table for each machine, it was suggested that i could select the records, > > put them into temp tables to minimize the query count. > > Something like : > > > > Select stuff > > from table > > > > Outer join machine > > where date_conditions > > into temptable > > > > > > However I am told Interbase (Firebird1.0) does not do temp-tables. > > > > At this point it looks like my best choice is: > > > > Select ID from machine where company = :company > > > > Then > > > > (while not eof) > > > > Select records from entry > > > > where dateconditions = blah > > > > and machineid = query1MachineID > > > > And then > > > > Select records from jobline > > > > Join job on blah > > > > Where dateconditions = blah > > > > And machineid = query1machineid > > > > (eof) > > > > > > > > This doesnt seem very efficient to me. Can any of the SQL gurus come up > > with a more efficient way of doing this? > > > > > > > > > > > > > > > > > > > > > > > > > > Tracey > > Software Developer / Web Master > > Logis > > [EMAIL PROTECTED] > > (025) 213-1065 > > > > > > -------------------------------------------------------------------------- > - > > New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] > > Website: http://www.delphi.org.nz > > To UnSub, send email to: [EMAIL PROTECTED] > > with body of "unsubscribe delphi" > > Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/ > > > > -------------------------------------------------------------------------- - > New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] > Website: http://www.delphi.org.nz > To UnSub, send email to: [EMAIL PROTECTED] > with body of "unsubscribe delphi" > Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/ > > --------------------------------------------------------------------------- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi" Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
