Prev: Running Profiler via a SQL script.
Next: Column naming standard � general opinions
From: Modern Benoni on 6 Nov 2009 12:36
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.

From: Modern Benoni on 6 Nov 2009 12:39

"Modern Benoni" <d4Nf6c4c5(a)benoni.com> wrote in message
news:hd1mqm$8lf$1(a)aioe.org...
>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.
>

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

From: Bob McClellan on 6 Nov 2009 14:37
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),108),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(a)benoni.com> wrote in message
news:hd1mqm$8lf$1(a)aioe.org...
>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.
>

From: Plamen Ratchev on 6 Nov 2009 20:25
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
 | 
Pages: 1
Prev: Running Profiler via a SQL script.
Next: Column naming standard � general opinions