SQL SERVER 2005 Self Joins output

Question

HI I am looking for some help I just can't seem to get my head how self joins work. I have this sql code

select Persno'Name',convert(char(20), A_Date , 13)'Logins',acode 
from atrail as LOGIN 
where acode = 'LOGIN' OR acode = 'LOGOUT'
order by a_date desc

Which produces a list of User names and when the have logged in and out. What I am trying to acheive is to get anther column with the LOGOUT date next to the date they logged in date so that it is easyer to see when they logged in and out.

Thanks

Andy

Answer 1

This should do it:

select      LOGIN.Persno  AS Name,
            LOGIN.a_date  AS login_date,    --//convert here as you like
            LOGOUT.a_date AS logout_date    --//convert here as you like

from        atrail as LOGIN

left join   atrail as LOGOUT
        ON  LOGOUT.Persno= LOGIN.Persno
        AND LOGOUT.acode = 'LOGOUT'

        AND LOGOUT.A_Date= (SELECT  TOP 1 x.A_Date
                            FROM    atrail x
                            WHERE   x.Persno = LOGIN.Persno
                                AND x.A_Date >= LOGIN.A_Date
                                AND x.acode = 'LOGOUT'
                            ORDER BY x.A_Date)
where       LOGIN.acode = 'LOGIN' 
order by    LOGIN.a_date desc

Will produce results in the format:

Name        login_date  logout_date
----------- ----------- -----------
pippo       2008-01-01  2008-01-03