Grouping Periods Of Dates - Sequential and Non-Sequential

[MSC]

Hi,

I'm trying to group assignments for people by the dates that assignment
started and ended.
eg, Bob has Assignment X 01-01-09 till 20-04-09,
Assignment Y 21-04-09 till 30-06-09, and
Assignment X (again) 01-07-09 till 31-07-09

If I use the Min/Max or First/Last functions within a select query it groups
the two X assignments starting 01-01-09 and end 31-07-09, but thats not a
true reflection of that assignment, not showing the break in between
(Assignment Y).

So, what I need is to group only sequential assignments - but don't know how
I would do that.

Hope I made sense. Any help appreciated.

Cheers

 

[KARL DEWEY]

What doe sthe data for those assignments look like? Table and field names
with datatype. Also post sample data.

 

[MSC]

Hi,

Table: U
Fields:
ID - Long Int
Name - Text
Role - Text
assgn_start - Date
assgn_end - Date
Allocation - Long Int
Program - Text

Example:
ID Name Role assgn_start assgn_end Allocation Program
994800 Owens, T BSA 01/01/2009 05/04/2009 100 Red
994800 Owens, T BSA 06/04/2009 01/05/2009 50 RM
994800 Owens, T BSA 06/04/2009 01/05/2009 50 Red
994800 Owens, T BSA 02/05/2009 05/06/2009 100 Red
994800 Owens, T BSA 06/06/2009 21/06/2009 100 Red
994800 Owens, T BSA 22/06/2009 31/07/2009 90 SII
994800 Owens, T BSA 22/06/2009 31/07/2009 10 Red

So, the only one that should be grouped is lines 4 & 5 02/05/09 till
21/06/09 as all other fields are the same and the dates are sequential, but,
what i get is the 'Red' prog from 01/01/09 till 21/06/2009.

Cheers

 

[MSC]

Hi,

Any luck - I'm having to do a manual correction at the moment, for the ones
I can tell are incorrect.

 

[KARL DEWEY]

I have been busy with other things and have not did anything on your project.
Maybe today.

 

[MSC]

Cheers

I have been busy with other things and have not did anything on your project.
Maybe today.

 

[MSC]

Hi,

Had a chance to look at this?

I have been busy with other things and have not did anything on your project.
Maybe today.