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
HTH,
Set
