sql logic help in sql server 2005

Hi I have a table with following sample data

  a    b        start          end               c
111      1      1/1/2011      1/2/2011             11
111      1      1/2/2011      1/6/2011             11
111      1      1/7/2011      12/31/9999      11
111      2      1/6/2011      1/7/2011              22
111  1      1/6/2011  2/1/2011          11

I want the output to be

111      1      1/1/2011      2/1/2011             11
111      1      1/7/2011      12/31/9999      11
111      2      1/6/2011      1/7/2011              22

logic for this is if a,b,c  are same if the end date and start date matches I want that in a single column

In above example In rows 1,2,5 a,b and c column matches and the end date of row 1 and start date of row 2  matches and end date of row 2 matches with start date of row 5 so I combined it to a single row

111      1      1/1/2011      2/1/2011             11

Can some one please help with the above logic

Thanks
vijay11Asked:
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.

micropc1Commented:
You could try using CTE recusion to join the end dates to the start dates. Something like....

WITH myCTE (a, b, c, start, [end], lvl)
AS
(
	--dates with child (connecting) dates
	SELECT ta.a
		, ta.b
		, ta.c
		, start
		, [end]
		, 0 as lvl
	FROM myTable ta
	WHERE start NOT IN (SELECT [end] FROM myTable WHERE a=ta.a and b=ta.b and c=ta.c)
	
	UNION ALL
	
	SELECT tb.a
		, tb.b
		, tb.c
		, tc.[start] start
		, tb.[end] [end]
		, lvl + 1
	FROM myTable tb
	INNER JOIN myCTE tc
	ON tc.a = tb.a
	AND tc.b = tb.b
	AND tc.c = tb.c
	WHERE tb.start = tc.[end]
)

SELECT a, b, c, start, [end]
FROM from myCTE ta
WHERE lvl = (SELECT MAX(lvl) FROM myCTE where a=ta.a and b=ta.b and c=ta.c)

UNION ALL

--single start/end dates
SELECT ta.a
	, ta.b
	, ta.c
	, start
	, [end]
FROM myTable ta
WHERE start NOT IN (select [end] FROM myTable WHERE a=ta.a AND b=ta.b AND c=ta.c)
AND [end] NOT IN (select [start] FROM myTable WHERE a=ta.a AND b=ta.b AND c=ta.c)

Open in new window



Output...

a           b           c           start                   end
----------- ----------- ----------- ----------------------- -----------------------
111         1           11          2011-01-01 00:00:00.000 2011-02-01 00:00:00.000
111         1           11          2011-01-07 00:00:00.000 9999-12-31 00:00:00.000
111         1           22          2011-01-06 00:00:00.000 2011-01-07 00:00:00.000

Open in new window



Another alternative would be to use joins, but if you don't know how many dates there will be in a set that would be difficult...
0

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
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 Development

From novice to tech pro — start learning today.