First, the procedure calculates the total number of time slices in the specified period. The procedure uses the T-SQL function DATEDIFF() to calculate the duration of the whole period in minutes and divides the result by the duration of the time slice, which the parameter @time_slice specifies. For example, in a 2-day period (@start = 1/1/2001, @end = 1/2/2001) with a specified time-slice duration of 60 minutes (@time_slice = 60), you'll have 48 time slices. If the duration of the time slice is greater than the duration of the time period, the procedure raises an error.
Next, the stored procedure uses two nested SELECT statements to calculate the resulting visitor activity. The inner SELECT statement uses the fn_sequence() function and the number of time slices calculated in the previous step to produce a sequence of date and time values. Each value in a sequence is the beginning of a particular time slice in a given period. If the duration of the time slice equals the duration of the time period (@slice_count = 0), then the inner SELECT statement returns only one row, corresponding to the whole period.
The outer SELECT statement joins the session_data table to the sequence that the inner SELECT returns. To calculate the number of sessions for each time slice, the code groups the resulting rowset by time-slice date and time. The JOIN operation determines whether the given time slice and the given session overlap. The first part of the JOIN clause—DATEDIFF(mi, dates.date, session_data.started) < @time_slice—determines that the session started before the given time slice finished (i.e., the difference in minutes between the session and the time slice starting times is less than the duration of the time slice). If the session started before the time slice began, DATEDIFF() returns a negative number, and the first part of the condition evaluates to TRUE.
The second part of the JOIN clause—DATEDIFF(mi, dates.date, COALESCE(session_data.finished, @end)) > 0—determines whether the session finished after the time slice started. The T-SQL function COALESCE() handles situations in which the session being analyzed is still active because the visitor hasn't signed off yet. The active session has the end date and time (i.e., the session_data.finished field) set to NULL. So if the session is inactive—the visitor has already signed off—the COALESCE() function returns the value of the session_data.finished field. If the session is active, COALESCE() returns the value of the @end parameter. Because all time slices, by definition, finish before the end of the time period, we assume that an active session finishes after any time slice in a period.
The outer SELECT statement uses the LEFT JOIN logical operator to ensure the return of all time slices. The statement returns a zero count for time slices that don't overlap with a session. The following example shows a use of the sp_get_activity stored procedure:
EXEC sp_get_activity '9:00 1/1/2001', '9:00 1/2/2001', 60
In this example, the hourly activity (time slice duration = 60) is calculated for a 24-hour period. Figure 2 shows the results that the stored procedure returns.
Sequences provide a simple and powerful alternative to loops and temporary tables traditionally used for data generation, and in some cases might significantly simplify your T-SQL code. And when you implement a sequence generator as a table-valued UDF in SQL Server 2000, the generator becomes a flexible and convenient tool for many SQL Server tasks.