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/
