Filter records with non-overlapping time [Solved]

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)

Filter records with non-overlapping time [Solved]

Author

Topic

ino mart
Starting Member

12 Posts

Posted - 2014-08-12 : 07:37:43

All

I have a table "tblActivity" with some activities, a start hour and an end hour. Furthermore, I have a table "tblSubscription" with the name of the subscriber and the PK of the course.

The table "tblActivity" contains e.g.
ID Course Start Stop
1 Yoga 09:00 10:00
2 Hockey 09:00 11:00
3 Football 09:30 12:30
4 Bowling 14:00 16:00
5 Fitness 12:00 12:45
6 Snooker 17:00 17:30
7 Baseball 11:00 11:50
8 Skating 08:00 08:50

I created a view and let's assume it currently has next records

Activity_ID User Start Stop
1 John 09:00 10:00
4 John 14:00 16:30
5 John 12:00 12:45

I need to write a query which only returns those activities which do not overlap in time with existing subscriptions for a specific user. In example above, if I run the query for John, it should return Snooker, Baseball and Skating.

How can this be done?

Regards
Ino

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-12 : 08:46:12

Note that it's a bad idea to use column names that are the same as SQL keywords. "Stop" is one example. You can tell in SSMS since it turns blue. I quoted it in the example below

Here's how I solved it:


declare @tblActivity table (ID int, Course varchar(20), Start time, [Stop] time)
declare @tblUsers table (Activity int, Name varchar(20), Start time, [Stop] time)
insert into @tblActivity (Id, Course, Start, [Stop]) values
(1, 'Yoga', '09:00', '10:00'),
(2, 'Hockey', '09:00', '11:00'),
(3, 'Football', '09:30', '12:30'),
(4, 'Bowling', '14:00', '16:00'),
(5, 'Fitness', '12:00', '12:45'),
(6, 'Snooker', '17:00', '17:30'),
(7, 'Baseball', '11:00', '11:50'),
(8, 'Skating', '08:00', '08:50')

insert into @tblUsers (Activity, Name, Start, [Stop]) values
(1, 'John', '09:00', '10:00'),
(4, 'John', '14:00', '16:30'),
(5, 'John', '12:00', '12:45')

select a.* from @tblActivity a
where 1 = all (
	select Case
	When u.[Stop] <= a.Start  -- user's activity stops before new activity starts
	   OR u.Start >= a.[Stop] -- user's activity starts after new activity stops
	then 1 
	end
	from @tblUsers u
	)

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-08-12 : 08:48:59

[code]
DECLARE @tblActivity TABLE
(
ID INT,
Course VARCHAR(50),
Start TIME,
[Stop] TIME
)

INSERT INTO @tblActivity (ID ,Course,Start,[Stop])
SELECT 1 AS ID,'Yoga' AS Course,'09:00' AS Start ,'10:00' as Stop UNION ALL
SELECT 2, 'Hockey', '09:00','11:00' UNION ALL
SELECT 3, 'Football', '09:30', '12:30' UNION ALL
SELECT 4, 'Bowling', '14:00', '16:00' UNION ALL
SELECT 5, 'Fitness', '12:00', '12:45' UNION ALL
SELECT 6, 'Snooker', '17:00', '17:30' UNION ALL
SELECT 7, 'Baseball', '11:00', '11:50' UNION ALL
SELECT 8, 'Skating', '08:00', '08:50'

DECLARE @tblUserActivity TABLE
(
Activity_ID INT,
[User] VARCHAR(50),
Start TIME,
[Stop] TIME
)

INSERT INTO @tblUserActivity (Activity_ID , [User], Start, [Stop])
SELECT 1 AS Activity_ID , 'John' AS [User] , '09:00' AS Start,'10:00' AS Stop UNION ALL
SELECT 4, 'John' ,'14:00' ,'16:30' UNION ALL
SELECT 5, 'John' ,'12:00' ,'12:45'

select
Course
from @tblActivity AS A
WHERE
NOT EXISTS
(
SELECT *
FROM @tblUserActivity UA
WHERE
UA.Start BETWEEN A.Start AND A.[STOP]
OR UA.[Stop] BETWEEN A.Start AND A.[STOP]
)

[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-08-12 : 08:50:56

I see it's already answer. Ignore my post

sabinWeb MCP

ino mart
Starting Member

12 Posts

Posted - 2014-08-12 : 10:58:19

Thanks. This is exactly what I need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources