To find the session-group end times, you need to nest another level inside the subquery in the SELECT list and use the technique I used in Listing 2 to obtain the start times. The innermost subquery makes sure that the end time is earlier than the minimum start time of the sessions the user opened and closed after that end time. I used the ISNULL() function here for a similar reason I used it earlier; here, it ensures that the end time of the last session the user closed is also included as a session group end time. Table 3 shows the output of Web Listing 2's query. Again, you can cross-check this output with Figure 1.
The Second Solution
Blank took a similar complex problem and made it fairly simple by an elegant use of views. The solution I present here is a slight revision of Blank's original solution. The first view, V_Start, returns the usernames and session group start times. You can create the V_Start view by running the script that Listing 3 shows.
The V_Start view returns all session start times in which no session by the same user started earlier and ended at or after that start time. In short, it returns all session-group start times. If you issue a SELECT * against the V_Start view, you'll get the output that Table 2 shows. The second view—V_End—returns usernames and session-group end times. The V_End view is similar to V_Start in that it returns all session end times, in which no session by the same user ended later and started at or before that start time. You can run the script that Listing 4 shows to create the V_End view. Issuing a SELECT * against the V_End view produces the output that Table 4 shows.
Now you just need to run the query in Listing 5 to get the desired results. The query selects all rows from the V_Start view, then uses a subquery in the SELECT list to retrieve the matching session-group end times. From V_End, the subquery returns the earliest session-group end time with the same user as in the outer query, along with an end time that's later than the start time in the outer query.
This solution differs from the first in two main respects. First, it uses a modular approach in which the initial problem is split into three subproblems: first, retrieving each session group's start time; second, retrieving each session group's end time; and third, matching session group start times to end times. The second difference is that this solution uses an existence check to track start and end times, whereas the previous solution uses subqueries that calculate aggregates. Queries that use the EXISTS() predicate are usually very efficient and easier to understand than their alternatives. In this case, however, both queries perform almost the same.
Ignoring Spaces
Some grouping-time-intervals problems have slightly more complex requirements than the ones I've discussed so far. For example, you might want to ignore spaces of up to a certain amount of time in which a user had no active session. In the session groups example, you could decide that if a certain session started no later than 5 minutes after another session ended, both sessions belong to the same session group. This rule requires minor revisions to the first solution I presented in Web Listing 2. In the subquery in the WHERE clause, you need to add 5 minutes to MAX(endtime) by using the DATEADD() function before you check whether the MAX(endtime) is earlier than the current start time. If it's earlier, the current start time is a session-group start time. Similarly, in the subquery in the SELECT list, you subtract 5 minutes from MIN(starttime) before you check whether that time is later than the current end time. Web Listing 3 shows the revised solution, with the revisions I made to Web Listing 2 highlighted in red. Note that in the output that Table 5 shows, the last three sessions User2 made are now grouped together in the same session group.
Similarly, you'd need to revise the V_Start and V_End views to implement the new requirements. You can use the DATEADD() function similarly to the way I used it in the first solution to add or subtract 5 minutes where appropriate. You can revise the views by running the script in Web Listing 4. You don't need to change the query that retrieves the data from the V_Start and V_End views. To check whether the revised views meet the new requirements, simply run the query that Listing 5 shows.
Wrap Up
I compared the performance of the two solutions for grouping time intervals and found that the first solution performs slightly better. However, the second solution is undoubtedly simpler to understand. With only a slight difference in performance, I'd choose the second solution over the first in a production environment. Next time you face a problem in T-SQL and you're thinking about using cursors, first try the techniques I demonstrated in my past three columns. And when you're programming in T-SQL, always think "set based."