Vlad wrote:

>> 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
>> ;
>
> Compare
>
> WITH
> Z as
> (
> 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
> ),
>
> Y as
> (
> 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 Z
> )
>
> 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 Y left join enquiries enq
> on enq.id = y.enq_bad_id
>
>
> Which one is easier to write and understand ? :)

Which is the reason I posed the ;)
My own SQL activity is based on what worked well 10 years ago and FIREBIRD is
capable of doing a lot better!

--
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