Solved

sql logic help in sql server 2005

Posted on 2012-04-10
1
318 Views
Last Modified: 2012-06-01
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
0
Comment
Question by:vijay11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 7

Accepted Solution

by:
micropc1 earned 500 total points
ID: 37831196
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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question