Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

IK1972

56 Posts

Posted - 2013-03-22 : 16:35:44

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-22 : 18:46:21

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)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-03-22 : 19:11:45

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources