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?
Sorry, Lester, I forgot the teaspoon...
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"
, sum(Case when "Time" = '9.00' then "Issued" else 0 end) as "9:00"
, sum(Case when "Time" = '9.25' then "Issued" else 0 end) as "9:15"
, sum(Case when "Time" = '9.50' then "Issued" else 0 end) as "9:30"
FROM Z
GROUP BY 1
I think this is what you're looking for,
Set
