Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » TSQL Help
Add to briefcase

TSQL Help Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
rembersu-432095
Posted Tuesday, February 19, 2013 3:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 32, Visits: 394
Hi 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. 



declare @Table1 table 
(id int,
StartDt datetime,
EndDt datetime)
insert into @Table1
select 1,'2009-11-28 00:00:00.000',''
union all
select 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 @Table1

declare @Table2 table 
(id int,
StartDt datetime,
EndDt datetime)		

insert into @Table2
select 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 all
select 1,'2006-09-05 00:00:00.000','2007-06-21 00:00:00.000'

select * from @Table2
Post #1421876
Evil Kraig F
Posted Tuesday, February 19, 2013 4:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:44 PM
Points: 5,677, Visits: 6,124
If 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.

declare @Table1 table 
(id int,
StartDt datetime,
EndDt datetime)
insert into @Table1
select 1,'2009-11-28 00:00:00.000',''
union all
select 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 @Table1

declare @Table2 table 
(id int,
StartDt datetime,
EndDt datetime)		

insert into @Table2
select 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 all
select 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 TestDT
FROM	@Table1
UNION ALL
SELECT id, EndDt AS TestDT
FROM	@Table1
)

SELECT DISTINCT
	pd.ID
FROM
	pivotedData AS pd
	LEFT JOIN
		@table2 AS t2
	ON	pd.TestDT >= t2.StartDt
		AND pd.TestDt <= t2.EndDt
WHERE
	t2.id IS NULL

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA
Post #1421884
rembersu-432095
Posted Tuesday, February 19, 2013 4:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 32, Visits: 394
Thank 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.


declare @Table1 table 
(id int,
StartDt datetime,
EndDt datetime)
insert into @Table1
select 1,'2009-11-28 00:00:00.000',''
union all
select 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 all
select 2,'2009-11-28 00:00:00.000',''
union all
select 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 @Table2
select 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 all
select 1,'2006-09-05 00:00:00.000','2007-06-21 00:00:00.000'
union all
select 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 all
select 2,'2006-09-05 00:00:00.000','2007-06-21 00:00:00.000'

;WITH pivotedData AS
(SELECT id, StartDt as TestDT
FROM	@Table1
UNION ALL
SELECT id, EndDt AS TestDT
FROM	@Table1
)
SELECT DISTINCT
	pd.ID
FROM
	pivotedData AS pd
	LEFT JOIN
		@table2 AS t2
	ON	pd.TestDT >= t2.StartDt
		AND pd.TestDt <= t2.EndDt
WHERE
	t2.id IS NULL
Post #1421894
Evil Kraig F
Posted Wednesday, February 20, 2013 10:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:44 PM
Points: 5,677, Visits: 6,124
A 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:

declare @Table1 table 
(RowID INT,
id int,
StartDt datetime,
EndDt datetime)
insert into @Table1
select 1, 1,'2009-11-28 00:00:00.000',''
union all
select 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 all
select 5, 2,'2009-11-28 00:00:00.000',''
union all
select 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 @Table2
select 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 all
select 1,'2006-09-05 00:00:00.000','2007-06-21 00:00:00.000'
union all
select 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 all
select 2,'2006-09-05 00:00:00.000','2007-06-21 00:00:00.000'

;WITH pivotedData AS
(SELECT RowID, id, StartDt as TestDT
FROM	@Table1
UNION ALL
SELECT RowID, id, EndDt AS TestDT
FROM	@Table1
)
SELECT DISTINCT
	pd.RowID
FROM
	pivotedData AS pd
	LEFT JOIN
		@table2 AS t2
	ON	pd.id = t2.id
		AND pd.TestDT >= t2.StartDt
		AND pd.TestDt <= t2.EndDt
WHERE
	t2.id IS NULL

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA
Post #1422210
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.