best way to query this - 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 2012 Forums

Transact-SQL (2012)

best way to query this

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-12-24 : 01:44:07

I have a talbe times with starttime and endtime , name (varchar fields with a time)

and I have data like

00:00 21:59 xyz
22:0 08:00 xyz

Now I want to run a query that will pull out per name if there are any overlapping records
(overlapping would be in the above case as if the bottom record says to 8 the top should be from 8)

what's the best way to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-24 : 03:53:29

SELECT t1.*
FROM dbo.Table1 AS t1
WHERE EXIST (SELECT * FROM dbo.Table1 AS w WHERE w.PersonID = t1.PersonID AND w.EndTime >= t1.StartTime AND w.StartTime <= t1.EndTime AND w.RowID < t1.RowID)

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources