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
ForresterCAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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
ForresterCAAuthor 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 AdministratorCommented:
Haven't we already done that with the answer to your previous question?  I must be missing something.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

ForresterCAAuthor 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
Brian CroweDatabase AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ForresterCAAuthor Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.