I have a query that is rather difficult. I have solved it, but I would like to solve it without creating an additional table or by using a cursor. In other words, I would like the query to be applied only to the two tables defined by the SQL code at the end of this discussion. Again, an additional table may not be created. I have a solution that involves creating an additional table, I do not want to do that. Also, queries involving cursors are not permitted.

One table will be called S_FROM_TO (for Supplier From-To)

Semantically, this table (S_FROM_TO) means the supplier with the number S# was under contract to supply parts from this day (and not on the day immediately before that day) to this day (and not on the day immediately after that day).

The data is as follows

S_FROM_TO

S# from to

1 1/4/2006 1/10/2006
2 1/2/2006 1/4/2006
2 1/7/2006 1/10/2006
3 1/3/2006 1/10/2006
4 1/4/2006 1/10/2006
5 1/2/2006 1/10/2006

Another table is called SP_FROM_TO (for Supplier of Parts FROM TO)

Semantically, this table (SP_FROM_TO) means Supplier S# supplied part P# from this date (and not on the day immediately before that day) to this day (and not on the day immediately after that day) .

SP_FROM_TO

S# P# from to

1 1 1/4/2006 1/10/2006
1 2 1/5/2006 1/10/2006
1 3 1/9/2006 1/10/2006
1 4 1/5/2006 1/10/2006
1 5 1/4/2006 1/10/2006
1 6 1/6/2006 1/10/2006
2 1 1/2/2006 1/4/2006
2 1 1/8/2006 1/10/2006
2 2 1/3/2006 1/3/2006
2 2 1/9/2006 1/10/2006
3 2 1/8/2006 1/10/2006
4 2 1/6/2006 1/9/2006
4 4 1/4/2006 1/8/2006
4 5 1/5/2006 1/10/2006

If SP_FROM_TO has a row showing supplier Sx as able to supply some specific part from day spf to day spt, then table S_FROM_TO shows the supplier as being under contract for that period. No row in SP_FROM_TO will show a time period for the supply of a part that is outside the corresponding suppliers contract period.

The question that is to be put into a query (Again, SQL that does not involve creating an additional table or SQL that involves a cursor) that involve the two tables above are:

Query: Get S#-FROM-TO rows for suppliers who have been unable to supply any parts at all during at least one interval of time, where FROM and TO together designate a maximal interval during which supplier S# was in fact unable to supply any part at all while under contract.

In other words, for the time each supplier is under contract, for what time periods did that supplier not supply a part.

This query involves both of the tables.

The result of your query should be:

S# from to
_________________________
2 1/7/2006 1/7/2006
3 1/3/2006 1/7/2006
5 1/2/2006 1/10/2006

So, I am looking for the SQL that returns the result set above.

For your convenience, I supply the code below to create and populate the tables. These are the only tables that can be used:

Create table S_FROM_TO(S# int, fromDate datetime, toDate datetime)
GO
insert into S_FROM_TO values(1, '1/4/2006', '1/10/2006')
insert into S_FROM_TO values(2, '1/2/2006', '1/4/2006')
insert into S_FROM_TO values(2, '1/7/2006', '1/10/2006')
insert into S_FROM_TO values(3, '1/3/2006', '1/10/2006')
insert into S_FROM_TO values(4, '1/4/2006', '1/10/2006')
insert into S_FROM_TO values(5, '1/2/2006', '1/10/2006')
GO

Create table SP_FROM_TO (S# int, P# int, fromdate datetime, todate datetime)
GO

insert into SP_FROM_TO VALUEs(1, 1, '1/4/2006','1/10/2006')
insert into SP_FROM_TO VALUEs(1, 2, '1/5/2006','1/10/2006')
insert into SP_FROM_TO VALUEs(1, 3, '1/9/2006','1/10/2006')
insert into SP_FROM_TO VALUEs(1, 4, '1/5/2006','1/10/2006')
insert into SP_FROM_TO VALUEs(1, 5, '1/4/2006','1/10/2006')
insert into SP_FROM_TO VALUEs(1, 6, '1/6/2006','1/10/2006')
insert into SP_FROM_TO VALUEs(2, 1, '1/2/2006','1/4/2006')
insert into SP_FROM_TO VALUEs(2, 1, '1/8/2006','1/10/2006')
insert into SP_FROM_TO VALUEs(2, 2, '1/3/2006','1/3/2006')
insert into SP_FROM_TO VALUEs(2, 2, '1/9/2006','1/10/2006')
insert into SP_FROM_TO VALUEs(3, 2, '1/8/2006','1/10/2006')
insert into SP_FROM_TO VALUEs(4, 2, '1/6/2006','1/9/2006')
insert into SP_FROM_TO VALUEs(4, 4, '1/4/2006','1/8/2006')
insert into SP_FROM_TO VALUEs(4, 5, '1/5/2006','1/10/2006')
GO

In SS2005 or above, the following query may be an option:

homerow,

Thanks for the query. I am looking at it now with a more extensive dataset, but it does give the correct answer for the data I supplied.

Thanks again