Lester Caine wrote:
> Do I end up with a list of fields each picking up one timeslot for a
> particular
> date? Or is there some way to use the 'timeslot' table that I used to use in
> the
> old two phase process?
OK falling at first hurdle ...
WITH
Z as
(
SELECT
CAST ( T.TRANSACT AS DATE ) AS "Date"
, ( EXTRACT ( HOUR FROM (T.TRANSACT - (T.PREVIOUS / 86400.000000) ) ) + (
EXTRACT ( MINUTE FROM (T.TRANSACT - (T.PREVIOUS / 86400.000000) ) ) / 15 ) /
4.00 ) AS "Time"
, COUNT( T.TICKET_NO ) AS "Issued"
, AVG( T.PREVIOUS ) / 60 AS "Ave_Wait"
FROM TRANSACTIONS T
GROUP BY 1, 2
)
SELECT "Date"
,( SELECT "Issued" FROM Z WHERE "Time" = '9.00' AND "Date" = ? ) AS "9:00"
,( SELECT "Issued" FROM Z WHERE "Time" = '9.25' AND "Date" = ? ) AS "9:15"
,( SELECT "Issued" FROM Z WHERE "Time" = '9.50' AND "Date" = ? ) AS "9:30"
FROM Z
ORDER BY 1
How do I get the sub-select to pick up each value from Z ?
--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/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
