Re:How to do it better
Quote
"Dominik F?ssler" <d.faess...@performa-software.ch> wrote in message
news:3b8e7f8d_2@dnews...
Quote
> I've the follow SQL:
> select m.BEZEICHNUNG, l.DATUM,
> (select sum((l1.zeitstunden * 60) + l1.zeitminuten) from leistungen l1
where
> l1.mitarbeiterid = m.autoid and l1.adressid = 0 and l.DATUM = l1.DATUM) as
> UPPROD,
> (select sum((l2.zeitstunden * 60) + l2.zeitminuten) from leistungen l2
where
> l2.mitarbeiterid = m.autoid and l2.adressid <> 0 and l.DATUM = l2.DATUM)
as
> PROD
> from mitarbeiter m, leistungen l
> where l.mitarbeiterid = m.autoid
> group by m.BEZEICHNUNG, l.DATUM
> order by m.BEZEICHNUNG
> Is there a better way to do this? Interbase has about 12 seconds.
> leistungen has about 5000 records
> mitarbeiter has about 10 records
Of course I cannot test, but I believe this should give you the correct
results using Join syntax and, assuming correctness, should give better
performance (as long as you also have appropriate indexes of course).
select m.BEZEICHNUNG, l.DATUM,
sum((l1.zeitstunden * 60) + l1.zeitminuten as UPPROD,
sum((l2.zeitstunden * 60) + l2.zeitminuten as PROD
from mitarbeiter m
join leistungen l on l.mitarbeiterid = m.autoid
join leistungen l1 on l.DATUM = l1.DATUM
join leistungen l2 on l.DATUM = l2.DATUM
where l1.mitarbeiterid = m.autoid and l1.adressid = 0
and l2.mitarbeiterid = m.autoid and l2.adressid <> 0
group by m.BEZEICHNUNG, l.DATUM
order by m.BEZEICHNUNG
--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
"Some see private enterprise as a predatory target to be shot, others as a
cow to be milked, but few are those who see it as a sturdy horse pulling the
wagon." - Winston Churchill
