Counting consecutive activities 1

[alexjones]

I have a group of students who, during the course of a school year, participate in one or more activities for varying lengths of time. The object of the game is, for each student, to report the number of consecutive activities in which he or she participated during the year. For example,

Year Student Activity StartDt EndDt
2009 A band 20080925 20090630
2009 A soccer 20080923 20090205
2009 A theater 20090319 20090501
2009 A chess 20090503 20090612,

student A would have had 3 consecutive activites: soccer, theater and chess. So I would want to see a result set with the values:

Year Student #Activities
2009 A 3

Any suggestions would be greatly appreciated.

 

[bborissov]

How you define "consecutive"?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

I believe it's when the dates not overlap as in the above sample. Another really tricky problem - I don't think we can solve it with self-join.

 

[markros]

select count(*), StudentID from (select *, row_number() over (partition by StudentID order by StartDate) as rn from myTable) X inner join (select *, row_number() over (partition by StudentID order by StartDate) rn from myTable) Y on X.rn = Y.rn+1 and X.Date_End < Y.Date_Start group by StudentID

not tested!!!!!!

 

[alexjones]

As markros surmised, I did define consecutive activities as non-overlapping. (I shall be more explicit in future.) It is also true that a self-join results in a set of pairwise consecutive activities. Not precisely what is required. For example, when I added one more activity to student A's already busy year:

Year Student Activity StartDt EndDt
2009 A band 20080925 20090630
2009 A soccer 20080923 20090205
2009 A theater 20090319 20090501
2009 A chess 20090503 20090612
2009 A bird-watching 20090419 20090430,

my self-join resulted in 5 pairwise consecutive activities:
soccer and theater
soccer and bird-watching
soccer and chess
theater and chess
bird-watching and chess

when what I'm really looking for are:
soccer, theater and chess
soccer, bird-watching and chess.

markros, I got an empty result after transcribing your suggested statement. I'll look at it more carefully. I am new to functionality introduced in SS2K5. I was wondering if a recursive or cumulative technique of some sort might be in order?

 

[markros]

Hi,

Here was my idea explained - I didn't have time to test it, since I'm struggling myself with a complex SP.

The idea is simple

Have All dates ordered - what's what Row_Number() does

StartDate EndDate
20080101 20081001 1
20080112 20081101 2
etc.

By joining 1 and 2
3 and 4 etc. we can check if it matches our condition on no overlap. However, looks like I needed to add T1.Rn %2 = 1 to make it work.

And actually, it may still not work - but this is the direction to think in, I believe.

 

[alexjones]

OK. I'll dig further. Thanks.