I have a table having classes start time and duration. So for example

Create Table MyClasses ( ClassName nvarchar(15), StartTime nvarchar(5),
Duration int );

Insert into MyClasses values ('Physics', '09:00', 40 );

Insert into MyClasses values ('Math', '11:30', 50 );

Insert into MyClasses values ('Chemistry', '13:30', 60 );

Insert into MyClasses values ('Biology', '16:00', 40 );



Now I want to write a query that gives my the free time between the classes.
Like

from to duration

Free Time Slot 1 9:40 11:30 1:50

Free Time Slot 1 12:20 13:30 1:10

Free Time Slot 1 14:30 16:00 1:30





Thanks in advance.

"Modern Benoni" <d4Nf6c4c5 (AT) benoni (DOT) com> wrote

I'm using SQL Server 2000 and no, this is not a homework assignment but a
real life problem that is very similar to this one.
Thanks

Here is one way...
Create Table MyClasses ( ClassName nvarchar(15), StartTime nvarchar(5),
Duration int );

Insert into MyClasses values ('Physics', '09:00', 40 );

Insert into MyClasses values ('Math', '11:30', 50 );

Insert into MyClasses values ('Chemistry', '13:30', 60 );

Insert into MyClasses values ('Biology', '16:00', 40 );


select ClassName,
[From] = StartTime,
[To] = left(convert(char,dateadd(n,duration,starttime),10 8),5),
Rn = (select count(mc.ClassName) from MyClasses mc where mc.StartTime <=
MyClasses.StartTime)
INTO #MC
from MyClasses


Select Class = 'FreeTimeSlot',
[From],
[To],
NextClass = (select [From] from #mc MC2 where MC.rn = MC2.rn-1)
into #MyClasses
From #MC MC

select *, Duration = rtrim(convert(char,datediff(n,[to],NextClass)/60,2)) +
':' + convert(char,datediff(n,[to],NextClass)%60,2)
from #MyClasses

drop table MyClasses
drop table #MC
drop table #MyClasses

hth,
...bob




"Modern Benoni" <d4Nf6c4c5 (AT) benoni (DOT) com> wrote

Here is one solution:

SELECT slot_start, slot_end,
DATEADD(MINUTE, DATEDIFF(MINUTE, slot_start, slot_end), 0) AS duration
FROM (
SELECT end_time AS slot_start,
(SELECT MIN(CAST(B.StartTime AS DATETIME))
FROM MyClasses AS B
WHERE CAST(B.StartTime AS DATETIME) > end_time) AS slot_end
FROM (
SELECT DATEADD(MINUTE, duration, CAST(StartTime AS DATETIME)) AS end_time
FROM MyClasses) AS A) AS S
WHERE slot_end IS NOT NULL;

/*

slot_start slot_end duration
----------------------- ----------------------- -----------------------
1900-01-01 09:40:00.000 1900-01-01 11:30:00.000 1900-01-01 01:50:00.000
1900-01-01 12:20:00.000 1900-01-01 13:30:00.000 1900-01-01 01:10:00.000
1900-01-01 14:30:00.000 1900-01-01 16:00:00.000 1900-01-01 01:30:00.000

*/

--
Plamen Ratchev
http://www.SQLStudio.com