SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL Help


TSQL Help

Author
Message
rembersu-432095
rembersu-432095
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 408
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



Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8579 Visits: 7660
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
rembersu-432095
rembersu-432095
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 408
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



Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8579 Visits: 7660
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search