Svein Erling Tysvær wrote:

> You want things possibly on one row to expand to several rows (e.g. if one row contains both logon and logoff). Then I cannot see any other option than using union. But you can do something very similar to what Norman writes that I think makes the statement easier to read (though it might just be my preference):
>
> WITH TMP_LOGON (STAFF_ID, TIMED, CLIENT, MYDATE) AS
> (SELECT MOD( LN.STAFF_ID, 1000)
> , SUBSTRING ( CAST (LN.LOGON AS TIME) FROM 1 FOR 8 )
> , R.TITLE
> , LN.LOGON
> FROM STAFF_LOG LN
> LEFT JOIN ROOMSTAT R ON MOD( LN.LOCATED, 64 ) = R.TERMINAL
> ),
> TMP_CALLCLEAR (STAFF_ID, TIMED, ACTION, TICKET, CLIENT, MYDATE) AS
> (SELECT MOD( T.STAFF_ID, 1000)
> , SUBSTRING ( CAST (T.TRANSACT AS TIME) FROM 1 FOR 8 )
> , CASE WHEN T.ROOM > 80 THEN 'Called' ELSE 'Cleared' END
> , T.TICKET_NO
> , C.SURNAME || ',' || C.FORENAME
> , T.TRANSACT
> FROM TRANSACTIONS T
> JOIN TICKET T2 ON T.TICKET_ID = T2.TICKET_ID
> JOIN CALLER C ON T2.CALLER_ID = C.CALLER_ID
> ),
> TMP_LOGOFF (STAFF_ID, TIMED, MYDATE) AS
> (SELECT MOD( LO.STAFF_ID, 1000)
> , SUBSTRING ( CAST (LO.LOGOFF AS TIME) FROM 1 FOR 8 )
> , LO.LOGOFF
> FROM STAFF_LOG LO
> ),
> TMP_UNION (STAFF_ID, TIMED, ACTION, TICKET, CLIENT, MYDATE) AS
> (SELECT STAFF_ID, TIMED, 'Logon', 'AT', CLIENT, MYDATE
> FROM TMP_LOGON
> UNION
> SELECT STAFF_ID, TIMED, ACTION, TICKET, CLIENT, MYDATE
> FROM TMP_CALLCLEAR
> UNION
> SELECT STAFF_ID, TIMED, 'Logoff', 'OFF', '-----', MYDATE
> FROM TMP_LOGOFF)
>
> SELECT S1.SURNAME || ',' || S1.FORENAME AS NAME, T.TIMED, T.ACTION, T.TICKET, T.CLIENT
> FROM TMP_UNION T
> JOIN STAFF S ON T.STAFF_ID = S.STAFF_ID
> WHERE T.MYDATE BETWEEN :FROM_DATE AND :FROM_DATE + 1
> AND S.TEAM = 1
> ORDER BY 1,2

Thanks SET ...

That does make it a little more 'user friendly'. I'm still stuck back in dbase
type SQL, and it was the WITH I was forgetting. But I was quite pleased that I
had the basic UNION stuff working at all ;)

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