Svein Erling Tysvær wrote:

> 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 ?
>
> Glad to see the fall wasn't serious, Lester!
>
> Rather than complicating things with subselects, what about:
>
> SELECT "Date"
> , Case when "Time" = '9.00' then "Issued" end as "9:00"
> , Case when "Time" = '9.25' then "Issued" end as "9:15"
> , Case when "Time" = '9.50' then "Issued" end as "9:30"
> FROM Z
> ORDER BY 1

But that will not give all of the time slots for a single day as one 'record' ?
I'm trying to 'twist' the time slots into a list of separate fields for each
date from the list of date/time slot records :( This just gives a 'diagonal' set
of data across the same number of line as the original ... or am I missing
something?

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