unordained wrote:

>> I'm too set in my ways which is why I need a kick in a different direction ;)
>>
>> http://lsces.co.uk/lsces/wiki/index.php?page=Rep-QueueServe
>> For example
>> Goes back quite a few years now, and still working, but it uses FastReports in
>> Builder6 and I've moved most of the stuff to web based in PHP. Reports are the
>> last area to sort out, and while I have the simple stuff these multi-level
>> ones are more of a problem. The ORIGINAL designs were broken up to get fast
>> response - with IB5.0 ....
>>
>> ( The image links on the website have been messed up by a 'software
>> improvement' so only a couple of the reports have the right pictures :( )
>
> I suggest the following, from my guessing at your table structures:
>
> In the first pass (z) we get totals per area and queue, counting up detail records only in some
> cases. (count(fieldname) only counts non-null entries, and I assume that case/when/end defaults to
> null if no conditions are matched -- otherwise, modifiy.)
> In the second pass (y) we calculate percentages (now that we have everything we need in one place)
> and also figure out who the worst offender was. We avoid multiple sub-queries by grabbing the id
> just once, rather than running several "first 1" queries to get each field we want.
> In the third pass (outermost) we join by the precalculated worst-offender id to go and get the
> details we wanted about it.
>
> I have not tested this query, but maybe it'll save you some time or give someone some ideas for
> solving their problems? It's certainly an interesting case.
>
> It also means that your <, between, and > sections in the report would be just one band grabbing
> three different fields and placing them vertically. The dataset has them horizontally. From your
> design-view screenshots, it looks like that's already the case.
>
> select a_name, q_name, enq_tot, enq_avg, enq_max, enq_low, enq_mid, enq_hi, pct_low, pct_mid,
> pct_hi, enq.serv_time, enq.serv_date, enq.ticket_num
> from (
> select a_name, q_name, enq_tot, enq_avg, enq_max, enq_low, enq_mid, enq_hi,
> case when enq_tot > 0 then 100 * enq_low / enq_tot else 0 end as pct_low,
> case when enq_tot > 0 then 100 * enq_mid / enq_tot else 0 end as pct_mid,
> case when enq_tot > 0 then 100 * enq_hi / enq_tot else 0 end as pct_hi,
> (select first 1 badenq.id from enquiries badenq where badenq.queue_id = q_id order by serv_time
> desc) as enq_bad_id
> from (
> select
> area.id a_id,
> area.name a_name,
> queue.id q_id,
> queue.name q_name,
> count(enquiries.*) enq_tot,
> avg(enquiries.serv_time) enq_avg,
> max(enquiries.serv_time) enq_max,
> count(case when enquiries.serv_time < area.lower_bound then 1 end) enq_low,
> count(case when enquiries.serv_time between area.lower_bound and area.upper_bound then 1 end)
> enq_mid,
> count(case when enquiries.serv_time > area.upper_bound then 1 end) enq_hi
> from
> enquiries
> left join queue on queue.id = enquiries.queue_id
> left join area on area.id = queue.area_id
> where
> enquiries.serv_date between ... and ...
> group by a_id, q_id, area.name, queue.name
> ) as z
> ) as y
> left join enquiries enq on enq.id = y.enq_bad_id
> ;

That last line is the sort of thing I would not have thought about, but seeing
it makes a lot of sense. Only those blocks with data in require the data to be
processed. And it does not really matter if the times for a block are
horizontal or vertical. Printing them is a different problem, and nowadays
they simply look at them on screen or dump to a spreadsheet anyway :)

Just loading the car up as I'm moving hardware on a site all weekend. Had not
expected SUCH a fast reply ;) But I'll try and plug the right fields in on
Monday. Ta

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/lsces/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php