wow that query takes forever...(26219ms) but it still doesnt bring back all machines. I have one machine which has no entries in entry, but has values in jobline. I need to see these machines as well.
----- Original Message ----- From: "Neven MacEwan" <[EMAIL PROTECTED]> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> Sent: Thursday, July 31, 2003 12:11 PM Subject: Re: [DUG]: Interbase SQL > Tracey > > I'm not a IB expert but what you want can be got by > (also your most you could probably do with adding date to > the FK Index for Entry.MachineID) > > SELECT > MACHINE.MachineID, > SUM(ENTRY.Quantity) as manhours, > SUM(ENTRY.Endquantity - ENTRY.Startquantity) as machinehours, > SUM(ENTRY.Fuel) as fuel, > (SELECT > SUM(JOBLINE .JoblineCost) > FROM > JOBLINE z JOIN > JOB y ON z.JobID = y.JobID > WHERE > z.JobDate BETWEEN Date1 AND Date2 AND > y.MachineID = MACHINE.MachineID) as jobcost > FROM > MACHINE LEFT JOIN > ENTRY ON ENTRY.MachineID = MACHINE.MachineID > WHERE > ENTRY.EntryDate BETWEEN Date1 AND Date2 > GROUP BY > MACHINE.MachineID > > > > > machine.ref > > > > machine.machineid > > > > > ----- Original Message ----- > From: "Tracey Maule" <[EMAIL PROTECTED]> > To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> > Sent: Thursday, July 31, 2003 11:38 AM > Subject: Re: [DUG]: Interbase SQL > > > > ok, I was fibbing a little... i had an index on jobline but none on > entry... > > Fixed that now (winking at the original developer) > > > > yes i will want these records grouped by machine. > > > > > > > > ----- Original Message ----- > > From: "Neven MacEwan" <[EMAIL PROTECTED]> > > To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> > > Sent: Thursday, July 31, 2003 11:04 AM > > Subject: Re: [DUG]: Interbase SQL > > > > > > > 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/ > > > > > > > -------------------------------------------------------------------------- > - > > 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/
