Get free meeting times from my table

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 2005 Forums

Transact-SQL (2005)

Get free meeting times from my table

Author

Topic

maxald
Starting Member

7 Posts

Posted - 2007-09-05 : 01:57:42

I have a table with registered meetings like this:


id time
1  3:00
1  4:00
2  3:00
2  5:00  
3  4:00

I also have a table that contains allowed meeting times:


id time
1  3:00
2  4:00
3  5:00

Now I need to know what meeting times are available for each id from my first table. This is the result I'd like to get from this example:


id  free
1  5:00
2  4:00
3  3:00
3  5:00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-05 : 02:05:10

[code]DECLARE @id TABLE
(
[id] int
)

INSERT INTO @id
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3

DECLARE @registered TABLE
(
[id] int,
[time] datetime
)

INSERT INTO @registered
SELECT 1, '3:00' UNION ALL
SELECT 1, '4:00' UNION ALL
SELECT 2, '3:00' UNION ALL
SELECT 2, '5:00' UNION ALL
SELECT 3, '4:00'

DECLARE @allowed TABLE
(
[id] int,
[time] datetime
)

INSERT INTO @allowed
SELECT 1, '3:00' UNION ALL
SELECT 2, '4:00' UNION ALL
SELECT 3, '5:00'

SELECT i.[id], a.[time]
FROM @id i
CROSS JOIN @allowed a
left JOIN @registered r
ON i.[id] = r.[id]
AND a.[time] = r.[time]
WHERE r.id IS NULL

/*
id time
----------- ------------------------------------------------------
1 1900-01-01 05:00:00.000
2 1900-01-01 04:00:00.000
3 1900-01-01 03:00:00.000
3 1900-01-01 05:00:00.000
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

maxald
Starting Member

7 Posts

Posted - 2007-09-05 : 02:50:54

Works beautifully, huge thanks!

I have been troubled by this problem for 2 days and you solved it in a minute... how did you do it? Plain knowledge!?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources