so i have the union query bringing back the correct records, but my issue is sorting them into a usable structure.
is there some way i can re-sort these returned records by machine once i have them?? > 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/ > --------------------------------------------------------------------------- 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/
