Sequential numbering of events/episodes for groups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sequential numbering of events/episodes for groups

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-12 : 09:34:24

Joley writes "I have searched the forums and have read Jeff Smith's article on detecting streaks or runs in your data
My task is to sequentially number episodes for each member_id in my database. Members may have more than one record and more than one episode, so I am numbering by member groups. Episodes are determined by the start_date and end_date. If there are 2 or more days between the end_date and next start_date for a member, then this constitutes a new episode for that member. If there are less than two days the end_date and the next start_date for a member, then this is assigned the same episode_id as the first record for that member.
I modified the SQL in Jeff Smith's article, however the issue I have is that his SQL involves Counting records, so that if a a member has 3 records and the first 2 are returned as episode "1", the second episode is numbered as "3" instead of "2".

If anyone can help me out I'd appreciate it.

Initial data set:

member_id,start_date,end_date
12345,2005-12-27,2006-01-09
12345,2005-03-06,2005-03-09
12345,2005-04-05,2005-04-07
67890,2005-08-03,2005-10-11
67890,2005-10-11,2005-11-01
67890,2005-12-01,2006-01-01

SQL statement:

SELECT *,
(SELECT COUNT(*)+1
FROM table1 t
WHERE t.member_id = tl.member_id
AND t.start_date <= tl.start_date
AND t.end_date + 2 <= tl.end_date) as episode_id
FROM table1 tl

Results:

member_id,start_date,end_date,episode_id
12345,2005-12-27,2006-01-09,1
12345,2005-03-06,2005-03-09,1
12345,2005-04-05,2005-04-07,2
67890,2005-08-03,2005-10-11,1
67890,2005-10-11,2005-11-01,1
67890,2005-12-01,2006-01-01,3"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-12 : 10:32:21

Hi Joley,

I feel as though this isn't the neatest method, but it does give the results I interpret from your description.

I can't seem to understand the results you've given though - they don't seem to match either your description or the query you were trying - so maybe I'm misunderstanding something.

--data
declare @table1 table (member_id int, start_date datetime, end_date datetime)
insert @table1
          select 12345, '20051227', '20060109'
union all select 12345, '20050306', '20050309'
union all select 12345, '20050405', '20050407'
union all select 67890, '20050803', '20051011'
union all select 67890, '20051011', '20051101'
union all select 67890, '20051201', '20060101'

--calculation
select *,
    (select count(*)+1-count(is_continuation) from
        (select a.*, case when b.member_id is not null then 1 end as is_continuation
        from @table1 a left outer join @table1 b on a.member_id = b.member_id and
            a.start_date < b.start_date and a.end_date > b.start_date - 2) b
    where member_id = a.member_id
      and start_date < a.start_date 
      and end_date < a.end_date - 2) as episode_id
from @table1 a
order by a.member_id, a.start_date

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources