How to get date ranges of islands of records? 2

ForresterCA
ForresterCA used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
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

Author

Commented:
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!
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
Haven't we already done that with the answer to your previous question?  I must be missing something.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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
Database Administrator
Top Expert 2005
Commented:
DECLARE @Table TABLE
(
	ID			INT IDENTITY(1,1),
	[Date]		DATE,
	Category	INT
);

INSERT @Table ([Date], Category)
VALUES ('20000101', 1),
	('20000102', 1),
	('20000104', 1),
	('20000106', 2),
	('20000108', 2),
	('20000110', 1),
	('20000112', 1);

WITH cteSource AS
(
	SELECT ID, [Date], Category,
		ROW_NUMBER() OVER(ORDER BY [Date]) AS RowNumber
	FROM @Table AS A
),
cteStartingPoint AS
(
	SELECT ID, [Date], Category, RowNumber,
		ROW_NUMBER() OVER(ORDER BY RowNumber) AS GroupNumber
	FROM cteSource AS A
	WHERE NOT EXISTS
	(
		SELECT *
		FROM cteSource AS B
		WHERE B.Category = A.Category
			AND B.RowNumber = A.RowNumber - 1
	)
),
cteEndingPoint AS
(
	SELECT ID, [Date], Category, RowNumber,
		ROW_NUMBER() OVER(ORDER BY RowNumber) AS GroupNumber
	FROM cteSource AS A
	WHERE NOT EXISTS
	(
		SELECT *
		FROM cteSource AS B
		WHERE B.Category = A.Category
			AND B.RowNumber = A.RowNumber + 1
	)
)
SELECT S.Category,
	ISNULL(DATEADD(DAY, 1, E1.[Date]), S.[Date]) AS DateStart,
	ISNULL(DATEADD(DAY, -1, S1.[Date]), E.[Date]) AS DateEnd
FROM cteStartingPoint AS S
INNER JOIN cteEndingPoint AS E
	ON S.Category = E.Category
	AND S.GroupNumber = E.GroupNumber
LEFT OUTER JOIN cteStartingPoint AS S1
	ON S.Category = 2
	AND S1.Category = 1
	AND S.GroupNumber = S1.GroupNumber - 1
LEFT OUTER JOIN cteEndingPoint AS E1
	ON S.Category = 2
	AND E1.Category = 1
	AND S.GroupNumber = E1.GroupNumber + 1

Open in new window

Author

Commented:
Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial