sql logic help in sql server 2005

Posted on 2012-04-10
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

Question by:vijay11
1 Comment

Accepted Solution

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)
	--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)
	SELECT tb.a
		, tb.b
		, tb.c
		, tc.[start] start
		, tb.[end] [end]
		, lvl + 1
	FROM myTable tb
	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)


--single start/end dates
	, 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


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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
error email attachment VB2010 5 39
Windows Drag & Drop Location 2 100
Which Microsoft MSDN Package Is The One I need? 3 45
Problem to open text file 11 110
This article shows how to make a Windows 7 gadget that extends its U/I with a flyout panel -- a window that pops out next to the gadget.  The example gadget shows several additional techniques:  How to automatically resize a gadget or flyout panel t…
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
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.…

810 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