Need SQL for finding gaps comparing two sets of pe

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)

Need SQL for finding gaps comparing two sets of pe

Author

Topic

idragoev
Starting Member

1 Post

Posted - 2008-02-27 : 05:23:21

Hi all,

I have two tables - Planning and Appointments:

Planning - contains a list of planned items. Used to define boundaries for a work day and defines based on type what can be done for each item.

Id,
TypeId - the type of the planned items
BeginTime DateTime - begin date and time of the planned item
EndTime DateTime - end date and time for the planned item

In the Planning table we can have as many records per day as we need:

1, First Meeting, 1 Jan 2008 09:00, 1 Jan 2008 11:00
2, First Meeting, 1 Jan 2008 11:00, 1 Jan 2008 12:00
3, First Meeting, 1 Jan 2008 13:00, 1 Jan 2008 15:00
4, First Meeting, 1 Jan 2008 15:00, 1 Jan 2008 18:00

Appointments - contanis a list with appointments

Id,
BeginTime DateTime
EndTime DateTime

1, 1 Jan 2008 09:00, 1 Jan 2008 09:30
2, 1 Jan 2008 10:00, 1 Jan 2008 11:00
3, 1 Jan 2008 11:00, 1 Jan 2008 11:30
4, 1 Jan 2008 14:00, 1 Jan 2008 15:30

What is needed?

What I need is to a find a way to compare the planned items with the appointments and to return all the periods for which a planned time exists:

Free planned time:

1, 1 Jan 2008 09:30, 1 Jan 2008 10:00
2, 1 Jan 2008 11:30, 1 Jan 2008 12:00
3, 1 Jan 2008 13:00, 1 Jan 2008 14:00
4, 1 Jan 2008 15:30, 1 Jan 2008 18:00

So, having two multitudes of periods,where the one specifies the planning templates and the other real used time, I need to find all the periods which can be used for another appointments.

I've tried several aproaches, but I always faced performance problems.

Thanks in advance.

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-27 : 11:04:10

Try this. I assume no planning period spans across days and that there are no overlaps in the planning periods.

DECLARE @Planning TABLE(Id int, TypeId varchar(50), BeginTime datetime, EndTime datetime)

INSERT @Planning
SELECT 1, 'First Meeting', '1 Jan 2008 09:00', '1 Jan 2008 11:00' UNION ALL
SELECT 2, 'First Meeting', '1 Jan 2008 11:00', '1 Jan 2008 12:00' UNION ALL
SELECT 3, 'First Meeting', '1 Jan 2008 13:00', '1 Jan 2008 15:00' UNION ALL
SELECT 4, 'First Meeting', '1 Jan 2008 15:00', '1 Jan 2008 18:00' UNION ALL
SELECT 5, 'First Meeting', '2 Jan 2008 15:00', '2 Jan 2008 18:00'

DECLARE @Appointments TABLE(Id int, BeginTime datetime, EndTime datetime)

INSERT @Appointments
SELECT 1, '1 Jan 2008 09:00', '1 Jan 2008 09:30' UNION ALL
SELECT 2, '1 Jan 2008 10:00', '1 Jan 2008 11:00' UNION ALL
SELECT 3, '1 Jan 2008 11:00', '1 Jan 2008 11:30' UNION ALL
SELECT 4, '1 Jan 2008 14:00', '1 Jan 2008 15:30' UNION ALL
SELECT 5, '2 Jan 2008 14:30', '2 Jan 2008 18:30';

WITH FreeTime(Id, Type, BeginTime, EndTime)
AS
(
SELECT P.Id, P.TypeId,
ISNULL(CASE WHEN A.BeginTime > P.BeginTime THEN P.BeginTime ELSE A.EndTime END, P.BeginTime),
ISNULL(CASE WHEN A.EndTime < P.EndTime THEN P.EndTime ELSE A.BeginTime END, P.EndTime)
FROM @Planning P LEFT OUTER JOIN @Appointments A
ON CONVERT(varchar,P.BeginTime,101) = CONVERT(varchar,A.BeginTime,101)
AND A.BeginTime < P.EndTime
AND A.EndTime > P.BeginTime
)
SELECT P.Id, P.TypeId, P.BeginTime, P.EndTime,
CASE WHEN Max(F.BeginTime) > Min(F.EndTime) THEN NULL ELSE Max(F.BeginTime) END AS FreeTimeBegin,
CASE WHEN Max(F.BeginTime) > Min(F.EndTime) THEN NULL ELSE Min(F.EndTime) END AS FreeTimeEnd
FROM @Planning P INNER JOIN FreeTime F ON P.Id = F.Id
GROUP BY P.Id, P.TypeId, P.BeginTime, P.EndTime

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources