identify sessions

[wer]

I have some call session files that have the following structure:

userid
call_type
call_reason
call_date
call_time

I want to identify groups of calls(issues) that begin in one month and may have carried over to the next month bu occured within 30 days of the first call. Here's some sample data:

userid;call_type;dob;call_reason;call_date;call_ti me
123456;1;4/1/9999;1;11/4/2006;15:32:43
123456;1 4/1/9999;1;11/5/2006;12:16:08
123456;1 4/1/9999;1;11/6/2006;12:16:08
123456;1;4/1/9999;1;12/21/2006;13:14:16
123456;1;4/1/9999;1;12/31/2006;13:14:16

Call 1 is within 30 days of calls 2 and 3 but not so for call 1 and calls 4 and 5. Can I, in one query, mark calls 1, 2 and 3 as being the same session or call?

Thanks.

[skhanal]

How do you handle the situation where the call date is 12/5/2006, which is within 30 days of 2 and 3 but not 1. That is one call can be a member of more than one set of 30 day group.

[wer]

The first call defines the set. There is an assumption by the business that any calls w/i 30 days of the first call must be a continuation of the first call. I don't agree w/ that, because there are calls that clustered in time and are probably not all the same call.

Call 1 begins the set. Calls 2 and 3 are within 30 days of call 1. Call 4 is not within 30 days of call 1 so it begins the next set which includes call 5.

I know I can do this via a loop or a cursor but wonder if there is a way to do it w/ a set based query.

Thanks.