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