Overlapping integer ranges ?

:confused: Dont know if this will be tough for the rest of you but for someone who is fairly new to SQL...I cannot figure it out...

I have a table:

Rownumber starttime endtime
1 l 30 l 240
2 l 40 l 120
3 l 50 l 260
4 l 1300 l 1400

Rows 1, 2, and 3 over lap with one another and I am trying to obtain the starttime and endtime values which can cover them all.

I would like to find the overlapping (starttime - endtime) ranges and accept the lowest starttime value and the highest endtime value.

Row 1: 30-------240
Row 2: 40-----120
Row 3: 50-------260
Row 4: ...1300---1440

I would like to include starttime-endtime ranges that do not overlap with any other integer range.

which in this case would be:

Rownumber starttime endtime
1 l 30 l 260
2 l 1330 l 1400

I was thinking of using a cursor and comparing each row to all of the other rows in the table and then setting a boolean in that row if it overlaps with another row in the table...is there a better way of doing this?

Thank you for the help!yes, there is a better way

"comparing each row to all of the other rows in the table" is as good a definition of a self-crossjoin as i can think of

a self-crossjoin would be written like this:
select t1.rownumber
, t1.starttime
, t1.endtime
, t2.rownumber
, t2.starttime
, t2.endtime
from yourtable as t1
cross
join yourtable as t2
now, with this as the basis, you can add a WHERE condition for overlapping ranges

e.g. WHERE t1.starttime BETWEEN t2.starttime and t2.endtime

you'd want to be careful about which pairs you want to keep, which would basically be the same idea as your "setting a boolean in that row if it overlaps" except that instead of setting a boolean, the t1 row survives the WHERE filter

make sense?|||What defines an "overlap" for your case? Some overlaps are obvious, since the beginning of one range sits inside of another, but what about more complex cases like:

1 50 250
2 200 450
3 300 650
4 500 800

Is this one range, two ranges, or something different?

-PatP|||I'd think the poster would want this as a result based on Pat's example:

1 50 450
4 500 800

Is my assumption correct?|||I'm just curious, but how the heck did you get there? That was a combination that I never thought of!

-PatP|||rdjabarov: You're close :) but the I am looking for only 50-800 (u can the view diagram below)

PatP: if that question was posed to me, this table is storing a list of times that are blocked off for the a day (i.e. hotel reservations, someone's personal schedule). (BUT Times can overlap in this case). So from the table of overlapping ( or non-overlapping) times (int), I need to find the times where either the entire range or part of the range sit inside another time.

i.e.

1 50 250 --entire range sits inside 2
2 200 450 --entire range sits inside 3
3 300 650 --part of this range (500-650) overlaps with 4
4 500 800 --and accordingly, (500-650) overlaps with 3

This is what I use to visualize it (where --- is a blocked off time):

For Date 07-26-2004:

0 1000

......... 50--------250
......................... 200------450 <sp>
................................300-------650
...................................... ........ 500-----800

so 50-800 would be the time range that would encompass all of the other time ranges since each one overlaps with another...

r937, thank you for your suggestion :)

not even sure how to begin with the where statement to achieve the above

is the select syntax supposed to be?:
select t1.rownumber
, t1.starttime
, t1.endtime
, t2.rownumber
, t2.starttime
, t2.endtime
from yourtable as t1
cross
join yourtable as t2
where t1.Rownumber = t2.rownumber

cause when I do just what you suggested (without the where t1.Rownumber = t2.rownumber)

I get:

Rownumber starttime endtime Rownumber starttime endtime
1 l 30 l 240 1 l 30 l 240
2 l 40 l 120 1 l 30 l 240
3 l 50 l 260 1 l 30 l 240
4 l 1300 l 1400 1 l 30 l 240
1 l 30 l 240 2 l 40 l 120
2 l 40 l 120 2 l 40 l 120
3 l 50 l 260 2 l 40 l 120
4 l 1300 l 1400 2 l 40 l 120
1 l 30 l 240 3 l 50 l 260
2 l 40 l 120 3 l 50 l 260
3 l 50 l 260 3 l 50 l 260
4 l 1300 l 1400 3 l 50 l 260
1 l 30 l 240 4 l 1300 l 1400
2 l 40 l 120 4 l 1300 l 1400
3 l 50 l 260 4 l 1300 l 1400
4 l 1300 l 1400 4 l 1300 l 1400|||Hmmmm...Any previous maximum endtime should be less than any consecutive minimum starttime...I think...Isn't it? But now that I am looking at it, if that's the logic then the only result you can get from Pat's example would be:

1 50 800|||Hey, you beat me to that, that's what I thought. Now the question is how to take into account ranges that DON'T overlap, because if you just do MIN(starttime) and MAX(endtime) it would skip non-overlapping ranges.|||awfdml, the first thing you should do is restate your requirements

a precise statement of requirements is crucial

for example, your first post said "I am trying to obtain the starttime and endtime values which can cover them all"

which can cover them all suggests that MIN() and MAX() is sufficient

but there will be holes, right?

and if you somehow want to see the holes...?

try to give a statement which defines all the start/end pairs you want|||The real problem comes from sequences like:

10-30
30-50
50-70
70-90
90-110
110-130
130-150
etc.

Each trip through the consolidation process brings new consolidations. I don't know of a way to express this using set notation without using iteration, so I think that you'll have to use an iterative (code based) solution.

At least in SQL-2000 you could solve the problem using a table valued function.

-PatP|||Pat: With the scenario you suggested, I would still consider each one overlapping since they share the sametimes

i.e. 10-30 (30 is shared, 50 is shared)...therefore the result would be 10-70
30-50
50-70

Think this would be what you're telling me, Pat?

30-50
40-60
50-70
90-110
110-120

30--50
....40--60
......50---70
......................90----110
...................................110----120

rdjabarov: I think the problem with doing a min and max is in the above scenario, like u stated :)

The result I would need to achieve in this case is:

First 3 times overlap with one another (one set), Last two overlap (second set) (but there is no overlap between)

30-70
90-120

r937: Yes, there will be holes (like above)

I definitely apologize if I did not clarify what I am looking for but I shall try :)

For a given table of blocked times, find all of the ranges(start-end) that overlap (partly, or entirely).

For each set of overlapping ranges, return* the range that will encompass the set.

If a range of the blocked times do not overlap with any another, return the the range.

*return-meaning store into a temp table or return as resultset

I.E.

Scenario 1: All ranges overlap

10-30
30-60
50-70
70-90

Result : 10-90

Scenario 2: Some ranges overlap with others, or not at all

20-40
35-55
40-60
70-90
85-100
150-200

Result: 20-60
70-100
150-200

Scenario 3: None of the times overlap

100-150
180-200
250-300

Result: 100-150
180-200
250-300

I am hoping that I do not have to use an iterative solution. (me in denial :rolleyes: )

The list of blocked times will not be big at all (most it could possibly be is 60 (per minute and NOT realistic)
For my case, the largest list of blocked times I would get could be anywhere from 12-20.

Still open to any suggestions of course as I think away:) Thank you all for your suggestions thus far! :D|||Forgot to ask Pat:

"At least in SQL-2000 you could solve the problem using a table valued function."

I am using SQL 2000, and this probably is a stupid question but what is a "table valued" function? Is it the user defined function?

:confused:

Thanks!|||Check UDF in BOL.|||In SQL-2000, User Defined Functions come in two flavors. Scalar functions return a single value, much like a C function would and are used anywhere an rvalue can be used like the SELECT list the WHERE clause, etc. Table valued functions actually return a table as their value and are used in the FROM clause almost exactly like you'd use a table.

-PatP|||just a dumb question, but what are your units?

your scenarios all use 20, 30, 55, etc.

is 1 the smallest unit here? perhaps 5? these are minutes, right?

because you did mention these were times, and i notice the largest value you're using is 1440 (which should be familiar to anyone who has ever worked with date and time values)|||You are correct r937, the units I am using are minutes and the smallest value is 0 and the largest of course is 1440.

i.e. 0-1440 in a range means entire day is blocked off|||right, well, what i was thinking of was to generate each of the 1440 different minutes, check it against all the ranges, and if no range covers it, then it's "available"

that can be done in a single query

what's next, though, is a bit harder, which is to assemble all the available minutes into "runs"

i.e. if the available minutes are 20, 21, 23, 24, 37, 38, 39
then you want to generate runs of 21-24 and 37-39|||thanks for the idea but I have a question:

would generating each minute be a performance issue? meaning that would I have to have some sort of a loop to check each minute?

I'm trying to watch out for performance since I'm doing this per day and allowing for a search for the available times for a range of dates...|||This is a pretty efficient way to do it. You will need a table of numbers to use it. It creates a string of periods then replaces periods with spaces in character positions within start and end times. Then it searches for occurances of ... ..... ...., that signify the beginning and ending of ranges (just like your number line diagram actually) and displays them.

DECLARE @.line VARCHAR(1500)

SELECT @.line = replicate('.',1500)

SELECT @.line = stuff(@.line,starttime,endtime - starttime,replicate(' ',endtime - starttime))
FROM times

SELECT N1.ID + 1 StartTime,
( SELECT TOP 1 ID + 1
FROM Numbers
WHERE ID > N1.ID
AND ID < len(@.line)
AND SubString(@.line,ID, 2) = ' .'
ORDER BY ID ) EndTime
FROM
Numbers N1
WHERE N1.ID < Len (@.line)
AND SubString(@.line,N1.ID, 2) = '. '

-- Test Data
CREATE TABLE times (rowid int identity, starttime int, endtime int)
INSERT times ( starttime, endtime ) VALUES (20,40)
INSERT times ( starttime, endtime ) VALUES (35,55)
INSERT times ( starttime, endtime ) VALUES (40,60)
INSERT times ( starttime, endtime ) VALUES (70,90)
INSERT times ( starttime, endtime ) VALUES (85,100)
INSERT times ( starttime, endtime ) VALUES (150,200)

StartTime EndTime
---- ----
20 60
70 100
150 200

DELETE FROM times
INSERT times ( starttime, endtime ) values (10,30)
INSERT times ( starttime, endtime ) values (30,60)
INSERT times ( starttime, endtime ) values (50,70)
INSERT times ( starttime, endtime ) values (70,90)

StartTime EndTime
---- ----
10 90

DELETE FROM times
INSERT times ( starttime, endtime ) values (100,150)
INSERT times ( starttime, endtime ) values (180,200)
INSERT times ( starttime, endtime ) values (250,300)
StartTime EndTime
---- ----
100 150
180 200
250 300

DELETE FROM times
INSERT times ( starttime, endtime ) values (180,200)
INSERT times ( starttime, endtime ) values (250,300)
StartTime EndTime
---- ----
180 200
250 300

--To make a Numbers table if you don't have one

SELECT TOP 8000 IDENTITY(INT) AS ID
INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2|||i am astounded

nice job, vaxman

;) ;) ;)|||High praise indeed. Thanks. I was going to extend it to work for multiple groups in the same dataset. That is a little tricky. Give it a whirl :) I'll put it up if I come up with something elegant.|||I said I would try to figure out a way to do this for multiple groups in the same dataset. This is one way. It's probably not the most efficient way to do it but it's passable.

-- Some test Data
SET NOCOUNT ON
CREATE TABLE times (rowid int identity, groupid int, starttime int, endtime int)
INSERT times ( groupid, starttime, endtime ) VALUES (1,20,40)
INSERT times ( groupid, starttime, endtime ) VALUES (1,35,55)
INSERT times ( groupid, starttime, endtime ) VALUES (1,40,60)
INSERT times ( groupid, starttime, endtime ) VALUES (1,70,90)
INSERT times ( groupid, starttime, endtime ) VALUES (1,85,100)
INSERT times ( groupid, starttime, endtime ) VALUES (1,150,200)
INSERT times ( groupid, starttime, endtime ) values (2,10,30)
INSERT times ( groupid, starttime, endtime ) values (2,30,60)
INSERT times ( groupid, starttime, endtime ) values (2,50,70)
INSERT times ( groupid, starttime, endtime ) values (2,70,90)
INSERT times ( groupid, starttime, endtime ) values (3,100,150)
INSERT times ( groupid, starttime, endtime ) values (3,180,200)
INSERT times ( groupid, starttime, endtime ) values (3,250,300)

-- Return a formated string representing time blocks.
CREATE FUNCTION f_GetLine(@.GroupID Int)
RETURNS VARCHAR(1500)
AS
BEGIN
DECLARE @.line VARCHAR(1500)
SELECT @.line = stuff(Coalesce(@.line,replicate('.',1500)),starttim e,
endtime - starttime,replicate(' ',endtime - starttime))
FROM times
WHERE GroupID = @.GroupID
RETURN @.line
END

-- Generate 106496 rows of test data.
While Coalesce(@.@.Rowcount,0) < 30000
INSERT times ( groupid, starttime, endtime ) select groupid, starttime, endtime from times

-- Results in 16 seconds. 6656 rows/second.
SELECT GroupId, N1.ID + 1 StartTime, (
SELECT TOP 1 ID + 1
FROM Numbers
WHERE ID > N1.ID
AND ID < len(o.line)
AND SubString(o.line,ID, 2) = ' .'
ORDER BY ID ) EndTime
FROM
Numbers N1, (
SELECT GroupId, dbo.f_GetLine(GroupID) line
FROM Times
GROUP BY GroupId
) o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
Order by GroupID

GroupId StartTime EndTime
---- ---- ----
1 20 60
1 70 100
1 150 200
2 10 90
3 100 150
3 180 200
3 250 300|||I installed SQL 2005 today and I thought I would test this script on it.
Performance was dismal on my Laptop. I realized it was doing a lot of unnecessary work.

Change the query for a single dataset to:

SELECT ID + 1 StartTime, charindex(' .',@.line, ID + 1) + 1 EndTime
FROM Numbers
WHERE ID < Len (@.line)
AND SubString(@.line,ID, 2) = '. '

And change the query for multiple groups in a dataset to:

SELECT GroupId, N1.ID + 1 StartTime, charindex(' .',o.line, N1.ID + 1) + 1 EndTime
FROM
Numbers N1, (
SELECT GroupId, dbo.f_GetLine(GroupID) line
FROM Times
GROUP BY GroupId
) o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
Order by GroupID

That version will churn through about 20,000 rows per second.|||A final comment about this. SQL 2005 performance was terrible even with the optimization above. It took 8 minutes to return a result. It was due to the ORDER BY. Not sure why, but rewriting it like this:

Declare @.Line Table (GroupID int, line Varchar(1500))

Insert Into @.line
SELECT GroupId , dbo.f_GetLine(GroupID) line
FROM Times
GROUP BY GroupId

SELECT GroupId, N1.ID + 1 StartTime,
charindex(' .',o.line, N1.ID + 1) + 1 EndTime
FROM
Numbers N1, @.line o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
order by GroupId

Resulted in less than 1 second response time for both SQL 2005 and SQL 2000 for 100,000 rows. I may try to put together a more precise test scenario for the 2005 behaivour.|||Regarding the cross join: No, you must not put t1.rowID = t2.rowID, as that would make it an inner join rather than a cross join; in fact it would compare each row to itself.

You can improve on this query a bit. First of all, you can add the opposite condition to the where clause, namely t1.rowID <> t2.rowID, as it isn't necessary to select rows to know that they overlap themselves. More importantly however, don't select just the rowID, as the only information you'll have is of the form

"the rows in this set that overlap are number 1, 2, 3, 4."

which helps you not at all. We need to know at least what row each one overlaps with to be any closer to establish the subsets. So you should select t2.rowID as well.

As an optimization, you can also add a condition to eliminate rows that don't add any information because another row covers both the start and end time, ie. if row 1 starts at 0 and ends at 10 there's no point selecting a row which starts at 5 and ends at 8. Thus we've got

select t1.rowID, t2.rowID as overlapRow
into #work
from times t1, times t2
where
t2.startTime BETWEEN t1.startTime AND t1.endTime
AND
t2.endTime NOT BETWEEN t1.startTime AND t1.endTime
AND
t1.rowID <> t2.rowID

We need to add those rows that are subsets by themselves, ie. those that don't overlap with any others;

insert into #work
select rowID, -1 from times where rowID not in (select rowID from #work union select overlapRow from #work)

The temp table #work now contains all the information necessary to establish the subsets. This however must be done iteratively or recursively. Perhaps the easiest is to add a subsetID column to the #work table (just select -1 in the two previous queries using the table) and use this to establish the subsets. I wish I could see immediately how to do it, but I can't. If you can figure it out, you can then finally produce the result with this query

select
min(t1.startTime) startTime,
max(case when t2.endTime > t1.endTime then t2.endTime else t1.endTime end) endTime
from
#work w
left join times t1 on t1.rowID = w.rowID
left join times t2 on t2.rowID = w.overlapRow
group by
subsetID
order by
startTime

I hope this helps!

Dag|||Thanks vaxman! That is pretty damn impressive :) I wish I looked at this over the weekend!! I have not had a chance to read through all of the suggestions but I do want to ask this: When creating the Numbers table why is there a reference to sysobjects? Maybe this requires me to research what sysobjects is/does but would I be able to create the Numbers table as just a temp table??

Thanks!|||vaxman:

gotta question...I am trying the solution with several different ranges and ran into a problem with a range of 0-n

I inserted 0 into the Numbers table and then inserted a 0-50 time range into the times table and after executing the select statement I get no rows...not sure how to fix that one...? I'm trying to tweak the select statement...wondering if you have any ideas?

thanks! :)|||Yeah that is because the string it builds starts at character position 1. You would have to modify it to subtract 1 from a few references to adjust for that. I'll do it later this evening.

The Numbers table was built as a product of just a bunch of random objects from sysobjects in order to generate some numbers. The objects themselves were irrevalent.

Other ways to do it in case the reference to sysobjects that freaks you out:

SELECT TOP 8000 ID = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3

Or

CREATE TABLE Numbers(ID INT NOT NULL PRIMARY KEY)
DECLARE @.Index INT
SET @.Index = 1
WHILE @.Index <= 8000
BEGIN
INSERT Numbers (ID) VALUES (@.Index)
SET @.Index = @.Index + 1
END

Or see http://www.bizdatasolutions.com/tsql/tblnumbers.asp for other examples.

You could create a temporary numbers table but if you are going to do that it might be better to create a view instead:

Create View Numbers AS
select (a0.id + a1.id + a2.id + a3.id) ID FROM
(select 0 id union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90) a1,
(select 0 id union select 100 union select 200 union select 300 union select 400 union select 500 union select 600 union select 700 union select 800 union select 900) a2,
(select 0 id union select 1000 union select 2000) a3

I suggest you just create a permanent table called Numbers though. It comes in handy for many things.|||i call it the integers table, instead of numbers

here's how you generate up to a million numbers
create table integers (i integer);
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4);
insert into integers (i) values (5);
insert into integers (i) values (6);
insert into integers (i) values (7);
insert into integers (i) values (8);
insert into integers (i) values (9);

select 100000*hk.i
+ 10000*tk.i
+ 1000*k.i
+ 100*h.i
+ 10*t.i
+ u.i as num
from integers hk
cross
join integers tk
cross
join integers k
cross
join integers h
cross
join integers t
cross
join integers u
you should be able to figure out how to cut that down to generate just 1440

sometimes i pre-populate the integers table with numbers 0 through 99 just so that i don't have to do a cross join|||Here is the updated version fixed to work with 0 start times.

SET NOCOUNT ON

CREATE TABLE times66 (rowid int identity, groupid int, starttime int, endtime int)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,0,40)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,35,55)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,40,60)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,70,90)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,85,100)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,150,200)
INSERT times66 ( groupid, starttime, endtime ) values (2,10,30)
INSERT times66 ( groupid, starttime, endtime ) values (2,30,60)
INSERT times66 ( groupid, starttime, endtime ) values (2,0,70)
INSERT times66 ( groupid, starttime, endtime ) values (2,70,90)
INSERT times66 ( groupid, starttime, endtime ) values (3,100,150)
INSERT times66 ( groupid, starttime, endtime ) values (3,1,200)
INSERT times66 ( groupid, starttime, endtime ) values (3,250,300)

GO

-- Return a formated string representing time blocks.
CREATE FUNCTION f_GetTimes66(@.GroupID Int)
RETURNS VARCHAR(1500)
AS
BEGIN
DECLARE @.line VARCHAR(1500)
SELECT @.line = stuff(Coalesce(@.line,replicate('.',1500)),
starttime + 2, endtime - starttime ,replicate(' ',endtime - starttime))
FROM times66
WHERE GroupID = @.GroupID
RETURN @.line
END
GO

-- Generate 106496 rows of test data.
While Coalesce(@.@.Rowcount,0) < 30000
INSERT times66 ( groupid, starttime, endtime ) select groupid, starttime, endtime from times66

Declare @.Line Table (GroupID int, line Varchar(1500))

Insert Into @.line
SELECT GroupId , dbo.f_GetTimes66(GroupID) line
FROM times66
GROUP BY GroupId

SELECT GroupId, N1.ID - 1 StartTime, charindex(' .',o.line, N1.ID ) - 1 EndTime
FROM
Numbers N1, @.line o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
ORDER BY GroupId

Drop Table times66

Drop FUNCTION f_Gettimes66

--To make a Numbers table if you don't have one.
/*
CREATE TABLE Numbers(ID INT NOT NULL PRIMARY KEY)
DECLARE @.Index INT
SET @.Index = 1
WHILE @.Index <= 8000
BEGIN
INSERT Numbers (ID) VALUES (@.Index)
SET @.Index = @.Index + 1
END
*/

GroupId StartTime EndTime
---- ---- ----
1 0 60
1 70 100
1 150 200
2 0 90
3 1 200
3 250 300|||Ooh! Ooh! Can I play?

Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from YourTable A
left outer join YourTable B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from YourTable A left outer join YourTable B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime|||blindman, that's even more gorgeous than all the other solutions so far combined

see, i knew it -- there's almost always a set-based solution to any problem that you think will yield only to programming/cursors/looping

nice job|||Thanks Rudy!
I've been up to my butt in Oracle crap for the last two weeks, but I gotta jump on the forums here every once in a while just to keep from getting rusty.|||blindman that is very cool no doubt! I can no doubt relate cuz i gotta do this in oracle too :)

thanks for the help!! :D|||Converting to PL/SQL is an easy two-step process:
Step 1: Look for all possible places in your code where a cursor should be completely unnecessary, and then add cursors there.
Stop 2: Rewrite your application requirements so that no actual output is required.
...and now you are done.|||hmmm...I'm getting an interesting error with the select statement by blindman..

I tried this with a temp table that I have with stored start and end times:

changed YourTable --> #final

Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime

ERROR:
Server: Msg 8118, Level 16, State 1, Line 3
Column 'StartTimeList.StartTime' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

? any ideas as to why I'm getting this...would this not work with temp tables?? Thanks :)|||The use of Min() implies aggregation, which means that any columns referenced "bare" (without an aggregate function) need to be included in the GROUP BY clause.

-PatP|||...so you need to add a GROUP BY clause at the end:

Select C, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime
group by Select StartTimeList.StartTime|||I have no clue how i screwed this up but I have a question:

Is this how its supposed to look (without the Select C?)

Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime
group by StartTimeList.StartTime

Im trying it out with a table that looks like:

starttime endtime
---- ----
0 20
10 20
15 25
25 60
75 100
150 500

When I run the select statement above...no rows are returned? I dont know if this has to do with the part of the select statement "B.StartTime is null" ??

Thanks for the help!|||What do you get if you run the subqueries independently?

select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null

...should return 0, 75, and 150.

select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null

...should return 60, 100, and 500.|||if i run them seperately, each one returns nothing... im thinking because in no range is the end time null??|||No. Its an outer join.

I'll try this out at work tomorrow and give you an answer tomorrow evening.|||Forgot to exclude identical records between the table instances. This works:

set nocount on
create table #Final (RecordID int, StartTime int, EndTime int)

Insert into #Final values (1, 0, 20)
Insert into #Final values (2, 10, 20)
Insert into #Final values (3, 15, 25)
Insert into #Final values (4, 25, 60)
Insert into #Final values (5, 75, 100)
Insert into #Final values (6, 150, 500)

Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime > B.StartTime and
A.StartTime <= B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A
left outer join #final B on A.EndTime >= B.StartTime and A.EndTime
< B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime
group by StartTimeList.StartTime

drop table #Final

---Output--
StartTime EndTime
---- ----
0 60
75 100
150 500|||worked like a charm! THANK YOU SOO MUCH blindman and to everyone that helped me with this issue! i hope to return the favor one day... :D