ive come up with this so far, but how can i have them ordered so that i get
F_1 - JOBCOST - MANHOURS - MACHINEHOURS - FORK entries1 0 7 5 3 jobcost1 456.98 0 0 3 entries1 0 106 10 5 f4 jobcost1 17745.80 0 0 f4 instead of F_1 - JOBCOST - MANHOURS - MACHINEHOURS - FORK entries1 0 7 5 3 entries1 0 106 10 5 f4 jobcost1 456.98 0 0 3 jobcost1 17745.80 0 0 f4 heres the query so far: SELECT 'jobcost1', sum(JOBLINE.Joblinecost) jobcost , sum(0) manhours, sum(0) machinehours, machine.ref fork FROM JOBLINE JOIN JOB ON JOBLINE.JobID = JOB.JobID join machine on machine.machineid = job.machineid WHERE JOBLINE.workDate BETWEEN '01/01/03' AND '07/01/03' group by machine.ref UNION SELECT 'entries1', sum(0) jobcost, sum(ENTRY.Quantity) manhours, sum(ENTRY.EndQuantity - ENTRY.StartQuantity) machinehours, machine.ref fork FROM ENTRY join machine on entry.machineid = machine.machineid WHERE ENTRY.EntryDate BETWEEN '01/01/03' AND '07/01/03' group by machine.ref ----- 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/
