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
