SQL Server 2008: Getting duration between user sessions

Question

I have this table

UserID   SessionID   SessionStart   SessionEnd
-----------------------------------------------
1        abc1        2010-1-1       2010-1-2
5        def3        2010-1-5       2010-1-9
1        llk0        2010-1-10      2010-1-11
5        spo8        2010-1-13      2010-1-15
1        pie7        2010-1-16      2010-1-29

I would like to be able to find the days between the end of one session to the start of the next session for each particular user.

So I am looking to get something like

UserID   DaysBetweenSessions
-----------------------------
1        8
1        5
5        4

Thanks!

Answer 1

Try:

DECLARE @YourTable table (UserID int, SessionId char(5), SessionStart datetime, SessionEnd datetime)
INSERT @YourTable VALUES (1,'abc1','2010-1-1 ','2010-1-2')
INSERT @YourTable VALUES (5,'def3','2010-1-5 ','2010-1-9')
INSERT @YourTable VALUES (1,'llk0','2010-1-10','2010-1-11')
INSERT @YourTable VALUES (5,'spo8','2010-1-13','2010-1-15')
INSERT @YourTable VALUES (1,'pie7','2010-1-16','2010-1-29')

;WITH AllStarts AS
(SELECT
     UserID, SessionEnd,row_number() over (partition by UserID order by SessionStart) as EndRank
     FROM @YourTable
)
, AllEnds AS 
(SELECT 
     UserID, SessionStart, row_number() over (partition by UserID order by SessionEnd) as StartRank
     FROM @YourTable
)
SELECT 
    s.UserID, DATEDIFF(day,s.SessionEnd,ISNULL(e.SessionStart,GETDATE())) AS DaysBetweenSessions
    FROM AllStarts              s
        LEFT OUTER JOIN AllEnds e on s.UserID = e.UserID and e.StartRank=s.EndRank+1
    --WHERE e.UserID is not NULL  --include to remove "ones in progress"

OUTPUT:

UserID      DaysBetweenSessions
----------- -------------------
1           8
1           5
1           103
5           4
5           117

(5 row(s) affected)

if you don't want to include the ones with out a matching next row (uncomment the WHERE) and get this result set:

UserID      DaysBetweenSessions
----------- -------------------
1           8
1           5
5           4

(3 row(s) affected)