Pavlo
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
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
Can you clarify the last statement with an example?
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!
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!