Self join in FM

[cognos05]

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

[navissar]

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.