Hi ,
I am having the below audit logon table , in which the data comes in below form
Primary key - Request 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 what I need is
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
Put the logon and log off time in same row for reporting and remove the duplicates.
I have done a self join in sql and achieved the results with the below query
Select A.COGIPF_REQUESTID,A.COGIPF_LOGON_OPERATION ,A.COGIPF_SESSIONID ,A.COGIPF_STATUS ,A.COGIPF_LOCALTIMESTAMP,A.COGIPF_USERID,B.COGIPF_REQUESTID,B.COGIPF_USERID,B.COGIPF_LOCALTIMESTAMP As LogOffTime,B.COGIPF_LOGON_OPERATION,B.COGIPF_USERID from
CognosDEVAuditDB.dbo.COGIPF_USERLOGON A Inner Join CognosDEVAuditDB.dbo.COGIPF_USERLOGON B on A.COGIPF_SESSIONID =B.COGIPF_SESSIONID
where A.COGIPF_STATUS ='Success'
And B.COGIPF_STATUS ='Success'
And A.COGIPF_REQUESTID < B.COGIPF_REQUESTID
So now can I use this same query in my FM Model or should I create a table in Sql Server and then include my new table ?
Any suggestions is appreciated.
Thanks,
Nithya
See how, in the SQL, you had to give each instance of the table a unique alias? So it's the same thing. treat them as two separate query subjects, and alias them.