Link to home
Start Free TrialLog in
Avatar of Pavlo
PavloFlag for Canada

asked on

How to get date ranges of islands of records? 2

Hello,

I have following data:
ID  Date  Category
1  1/1/2000  1
2  1/2/2000  1
3  1/4/2000  1
4  1/6/2000  2
5  1/8/2000  2
6  1/10/2000 1
7  1/12/2000 1

the result should be:
Category  DateStart  DateEnd
1                1/1/2000   1/4/2000
2                1/5/2000   1/9/2000    
1                1/10/2000   1/12/2000

Briefly, Category 2 (if any records exist with this Category) must fit into interval, not occupied by Category 1

Please help
Thank you
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Can you clarify the last statement with an example?

Briefly, Category 2 (if any records exist with this Category) must fit into interval, not occupied by Category 1
Avatar of Pavlo

ASKER

It means that islands of Category 1 are:
1/1 - 1/4 and 1/10 - 1/12
so interval between these 2 islands is 1/5 - 1/9

if any records from Category 2 exist between 1/5 and 1/9,
then I need to show this interval as one that belongs to Category 2

would be great if this is doable
Thank you!
Haven't we already done that with the answer to your previous question?  I must be missing something.
Avatar of Pavlo

ASKER

no, here is the difference:

for question 1 the correct result is:
Category  DateStart  DateEnd
1                1/1/2000   1/4/2000
2                1/6/2000   1/8/2000  
1                1/10/2000   1/12/2000

for question 2 the correct result is:
Category  DateStart  DateEnd
1                1/1/2000   1/4/2000
2                1/5/2000   1/9/2000  
1                1/10/2000   1/12/2000
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Pavlo

ASKER

Thank you!