InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 17:09:42 GMT20http://www.sqlservercentral.com/Forums/Topic1421876-392-1.aspxA simple enough change, but you still need a key row on table1 to know the exact row. See the modified code below as well as my slightly modified table so you can identify the exact culprit rows:[code="sql"]declare @Table1 table (RowID INT,id int,StartDt datetime,EndDt datetime)insert into @Table1select 1, 1,'2009-11-28 00:00:00.000',''union allselect 2, 1,'2009-11-07 00:00:00.000','2009-11-27 00:00:00.000'union all select 3, 1,'2009-07-01 00:00:00.000','2009-11-06 00:00:00.000'union all select 4, 1,'2009-06-19 00:00:00.000','2009-06-30 00:00:00.000'union allselect 5, 2,'2009-11-28 00:00:00.000',''union allselect 6, 2,'2009-11-07 00:00:00.000','2009-11-27 00:00:00.000'union all select 7, 2,'2009-07-01 00:00:00.000','2009-11-06 00:00:00.000'union all select 8, 2,'2009-06-19 00:00:00.000','2009-06-30 00:00:00.000'declare @Table2 table (id int,StartDt datetime,EndDt datetime) insert into @Table2select 1,'2008-11-05 00:00:00.000','2009-11-06 00:00:00.000'union all select 1,'2007-11-29 00:00:00.000','2008-11-04 00:00:00.000'union all select 1,'2007-09-04 00:00:00.000','2007-09-04 00:00:00.000'union all select 1,'2007-07-02 00:00:00.000','2007-07-12 00:00:00.000'union allselect 1,'2006-09-05 00:00:00.000','2007-06-21 00:00:00.000'union allselect 2,'2008-11-05 00:00:00.000','2009-11-06 00:00:00.000'union all select 2,'2007-11-29 00:00:00.000','2008-11-04 00:00:00.000'union all select 2,'2007-09-04 00:00:00.000','2007-09-04 00:00:00.000'union all select 2,'2007-07-02 00:00:00.000','2007-07-12 00:00:00.000'union allselect 2,'2006-09-05 00:00:00.000','2007-06-21 00:00:00.000';WITH pivotedData AS(SELECT RowID, id, StartDt as TestDTFROM @Table1UNION ALLSELECT RowID, id, EndDt AS TestDTFROM @Table1)SELECT DISTINCT pd.RowIDFROM pivotedData AS pd LEFT JOIN @table2 AS t2 ON pd.id = t2.id AND pd.TestDT >= t2.StartDt AND pd.TestDt <= t2.EndDtWHERE t2.id IS NULL[/code]Wed, 20 Feb 2013 10:49:48 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1421876-392-1.aspxThank you for the Reply!.. my criteria is that we would have mutiple records (Start and EndDt's) in the @Table1 for single ID and also the same in the @table2. Your query pickup the records the correct records from @Table1 who's date are out of range. below is the data that i have. i would need the out from the @Table1 (ID,StartDt,EndDt) which dont fall in the range of the @table2. Thank you once again for taking time to help me out.[code="sql"]declare @Table1 table (id int,StartDt datetime,EndDt datetime)insert into @Table1select 1,'2009-11-28 00:00:00.000',''union allselect 1,'2009-11-07 00:00:00.000','2009-11-27 00:00:00.000'union all select 1,'2009-07-01 00:00:00.000','2009-11-06 00:00:00.000'union all select 1,'2009-06-19 00:00:00.000','2009-06-30 00:00:00.000'union allselect 2,'2009-11-28 00:00:00.000',''union allselect 2,'2009-11-07 00:00:00.000','2009-11-27 00:00:00.000'union all select 2,'2009-07-01 00:00:00.000','2009-11-06 00:00:00.000'union all select 2,'2009-06-19 00:00:00.000','2009-06-30 00:00:00.000'declare @Table2 table (id int,StartDt datetime,EndDt datetime) insert into @Table2select 1,'2008-11-05 00:00:00.000','2009-11-06 00:00:00.000'union all select 1,'2007-11-29 00:00:00.000','2008-11-04 00:00:00.000'union all select 1,'2007-09-04 00:00:00.000','2007-09-04 00:00:00.000'union all select 1,'2007-07-02 00:00:00.000','2007-07-12 00:00:00.000'union allselect 1,'2006-09-05 00:00:00.000','2007-06-21 00:00:00.000'union allselect 2,'2008-11-05 00:00:00.000','2009-11-06 00:00:00.000'union all select 2,'2007-11-29 00:00:00.000','2008-11-04 00:00:00.000'union all select 2,'2007-09-04 00:00:00.000','2007-09-04 00:00:00.000'union all select 2,'2007-07-02 00:00:00.000','2007-07-12 00:00:00.000'union allselect 2,'2006-09-05 00:00:00.000','2007-06-21 00:00:00.000';WITH pivotedData AS(SELECT id, StartDt as TestDTFROM @Table1UNION ALLSELECT id, EndDt AS TestDTFROM @Table1)SELECT DISTINCT pd.IDFROM pivotedData AS pd LEFT JOIN @table2 AS t2 ON pd.TestDT >= t2.StartDt AND pd.TestDt <= t2.EndDtWHERE t2.id IS NULL[/code]Tue, 19 Feb 2013 16:56:39 GMTrembersu-432095http://www.sqlservercentral.com/Forums/Topic1421876-392-1.aspxIf I understood your requirements, the following is what you'll need. Offhand about your test data, everything had an id of 1 before I modified it.Short form of what this does: Pivot the test data, check each value against the validation set, and return outliers. Only return each ID once in case of multiple failures.[code="sql"]declare @Table1 table (id int,StartDt datetime,EndDt datetime)insert into @Table1select 1,'2009-11-28 00:00:00.000',''union allselect 2,'2009-11-07 00:00:00.000','2009-11-27 00:00:00.000'union all select 3,'2009-07-01 00:00:00.000','2009-11-06 00:00:00.000'union all select 4,'2009-06-19 00:00:00.000','2009-06-30 00:00:00.000'select * from @Table1declare @Table2 table (id int,StartDt datetime,EndDt datetime) insert into @Table2select 1,'2008-11-05 00:00:00.000','2009-11-06 00:00:00.000'union all select 2,'2007-11-29 00:00:00.000','2008-11-04 00:00:00.000'union all select 3,'2007-09-04 00:00:00.000','2007-09-04 00:00:00.000'union all select 4,'2007-07-02 00:00:00.000','2007-07-12 00:00:00.000'union allselect 5,'2006-09-05 00:00:00.000','2007-06-21 00:00:00.000'select * from @Table2;WITH pivotedData AS(SELECT id, StartDt as TestDTFROM @Table1UNION ALLSELECT id, EndDt AS TestDTFROM @Table1)SELECT DISTINCT pd.IDFROM pivotedData AS pd LEFT JOIN @table2 AS t2 ON pd.TestDT >= t2.StartDt AND pd.TestDt <= t2.EndDtWHERE t2.id IS NULL[/code]Tue, 19 Feb 2013 16:12:16 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1421876-392-1.aspxHi all,Need some help in writing a query for the below scenario. I have two table with start and end dates which can be joined on a common ID flied. I would like to extract out all the Records from the table @Table1 who’s start and End dates do don’t fall in any range in the table @Table2 start and end dates. [code="sql"]declare @Table1 table (id int,StartDt datetime,EndDt datetime)insert into @Table1select 1,'2009-11-28 00:00:00.000',''union allselect 1,'2009-11-07 00:00:00.000','2009-11-27 00:00:00.000'union all select 1,'2009-07-01 00:00:00.000','2009-11-06 00:00:00.000'union all select 1,'2009-06-19 00:00:00.000','2009-06-30 00:00:00.000'select * from @Table1declare @Table2 table (id int,StartDt datetime,EndDt datetime) insert into @Table2select 1,'2008-11-05 00:00:00.000','2009-11-06 00:00:00.000'union all select 1,'2007-11-29 00:00:00.000','2008-11-04 00:00:00.000'union all select 1,'2007-09-04 00:00:00.000','2007-09-04 00:00:00.000'union all select 1,'2007-07-02 00:00:00.000','2007-07-12 00:00:00.000'union allselect 1,'2006-09-05 00:00:00.000','2007-06-21 00:00:00.000'select * from @Table2[/code]Tue, 19 Feb 2013 15:33:47 GMTrembersu-432095