Sql Inner joins or joins itself
I have a sql table populated with a program and the data will look like this:
Primary Key - Query ID Columns
Request ID Session ID UserID LoginOperation Time
1 1 Arun Logon 8:00
2 1 Arun Logoff 8:30
3 2 Sandy Logon 7:55
4 2 Sandy Logon Expired 8:38
Now i need
Request ID Session ID UserID LoginOperation Time Login Operation Time
1 1 Arun Logon 8:00 Logoff 8:30
3 2 Sandy Logon 7:55 LogonExpired 8:38
SO I will need my login information along with the time log on the same line.
How can I achieve this?
Should I filter my first table with login and then do the same for the second table with either logff or logonexpired and then inner join of both tables with session id.
Or I can do my own joining to these tables with some condition ..
Please let me know your suggestions?
Hello,
I came up with the following query to get my log information and logout time.
Select T.COGIPF_LOCALTIMESTAMP, T.COGIPF_SESSIONID, T.COGIPF_REQUESTID,
T.COGIPF_STATUS, T.COGIPF_LOGON_OPERATION, T.COGIPF_USERNAME,
T.COGIPF_USERID, T.COGIPF_NAMESPACE, T.COGIPF_CAMID,
Z.COGIPF_LOCALTIMESTAMP as LogOffTime
from [CognosAuditSampleDev].COGIPF_USERLOGON T
LEFT JOIN [CognosAuditSampleDev].COGIPF_USERLOGON Z on
T.COGIPF_SESSIONID = Z.COGIPF_SESSIONID and
Z.COGIPF_LOGON_OPERATION <>'Logon'
where T.COGIPFSTATUS='Success' And Z.COGIPFSTATUS ='Success' And
T.COGIPF_LOGON_OPERATION='Logon'
I encountered an issue where there was no logg off for the login, in which case the log entry is missing from my table even after I made a left join. Please let me know where I am going wrong.
Thanks, Manikandan
source to share
SELECT r1.RequestID, r1.SessionID, r1.UserId, r1.LoginOperation, r1.Time
, r2.LoginOperation, r2.Time
FROM Requests r1
INNER JOIN Requests r2 on r2.SessionID = r1.SessionID and r2.LoginOperation <> 'Logon'
WHERE r1.LoginOperation = 'Logon'
You already mentioned self-joining in the question ... you should just try :)
source to share
You can do this easily by using self INNER JOIN
and comparing sessionID and requestID. For example:
SELECT t1.RequestID, t1.SessionID, t1.UserID, t1.LoginOperation, t1.Time,
t2.LoginOperation, t2.Time
FROM Test t1
INNER JOIN Test t2 on t2.SessionID = t1.SessionID
WHERE t1.RequestID < t2.RequestID
This was tested on SQL Fiddle .
Enjoy!
source to share
If a session can only have one login and logout event (that is, if each login starts a new session), you can skip the connection entirely and use conditional selection and the max () function to flatten the result:
SELECT
MAX(CASE WHEN LoginOperation = 'Logon' THEN RequestID END) RequestID,
SessionID, UserID,
MAX(CASE WHEN LoginOperation = 'Logon' THEN LoginOperation END) LoginOperation1,
MAX(CASE WHEN LoginOperation = 'Logon' THEN Time END) Time1,
MAX(CASE WHEN LoginOperation <> 'Logon' THEN LoginOperation END) LoginOperation2,
MAX(CASE WHEN LoginOperation <> 'Logon' THEN Time END) Time2
FROM your_table
GROUP BY SessionID, UserID
And if we can assume that the login always happens before the logout, and that there will always be a pair, you can reduce it to:
SELECT
MIN(RequestID) RequestID,
SessionID, UserID, 'Logon' as LoginOperation1,
MIN(Time) Time1,
MAX(CASE WHEN LoginOperation <> 'Logon' THEN LoginOperation END) LoginOperation2,
MAX(Time) Time2
FROM your_table
GROUP BY SessionID, UserID
source to share