SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IK1972
Yak Posting Veteran

54 Posts

Posted - 03/22/2013 :  16:35:44  Show Profile  Reply with Quote
Hi

I have 2 tables I need display all rows from #temp table where row exists in #tempPool and #tempPool TCreatedDate is in 5 mintue range on #temp TCreatedDate.

Basiclay I need all #temp table rows where with in 5 mintues row created in #tempPool.

Thanks

create table #temp(LN varchar(25), TID bigint, TCreatedDate datetime)

insert into #temp values
('286213560',250760,'2013-02-28 11:57:18.923'),
('249542563',254138,'2013-03-05 05:35:35.267'),
('249139014',159693,'2012-07-18 22:35:13.490'),
('258850775',225535,'2013-01-23 13:18:39.120'),
('259426838',164059,'2012-07-30 13:15:38.770'),
('216303216',238430,'2013-02-25 06:13:52.720'),
('258845659',141062,'2012-07-10 04:17:14.913'),
('289216605',194268,'2013-01-28 09:48:17.617'),
('292934825',217258,'2012-12-18 12:02:15.193'),
('283129816',232465,'2013-01-29 09:46:00.457'),
('286208064',166559,'2012-07-31 09:53:58.750')



create table #tempPool(TID bigint, TPool varchar(20), TCreatedDate datetime)

insert into #tempPool values
(250760,'A','2013-02-28 12:57:18.923'),
(254138,'B','2013-03-05 05:34:35.267'),
(159693,'C','2012-07-18 22:37:13.490'),
(164059,'D','2012-07-30 15:15:38.770'),
(238430,'E','2013-02-25 07:13:52.720'),
(141062,'F','2012-07-10 05:17:14.913')

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 03/22/2013 :  18:46:21  Show Profile  Reply with Quote
Do you mean this or a variation of this?
select * from #temp a
where exists (select * from #tempPool b 
where b.TID = a.TID and ABS(datediff(mi,a.TcreatedDate, b.TCreatedDate)) <= 5)
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
437 Posts

Posted - 03/22/2013 :  19:11:45  Show Profile  Reply with Quote
This could perform better, esp. if you were wise enough to cluster #temp on TCreatedDate:


SELECT t.*
FROM #temp t
INNER JOIN #tempPool tp ON
    t.TID = tp.TID AND
    t.TCreatedDate >= DATEADD(MINUTE, -5, tp.TCreatedDate) AND
    t.TCreatedDate <= DATEADD(MINUTE, 5, tp.TCreatedDate)

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000