 |
|
| Computers Forum Index » Computer - Databases - MS SQL Server » How to determine free time... |
|
Page 1 of 1 |
|
| Author |
Message |
| Modern Benoni... |
 Posted: Fri Nov 06, 2009 10:36 pm |
|
|
|
Guest
|
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. |
|
|
| Back to top |
|
|
|
| Modern Benoni... |
| Posted: Fri Nov 06, 2009 10:39 pm |
|
|
|
Guest
|
"Modern Benoni" <d4Nf6c4c5 at (no spam) benoni.com> wrote in message
news:hd1mqm$8lf$1 at (no spam) aioe.org...
Quote: 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 |
|
|
| Back to top |
|
|
|
| Bob McClellan... |
| Posted: Sat Nov 07, 2009 12:37 am |
|
|
|
Guest
|
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 at (no spam) benoni.com> wrote in message
news:hd1mqm$8lf$1 at (no spam) aioe.org...
Quote: 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.
|
|
|
| Back to top |
|
|
|
| Plamen Ratchev... |
| Posted: Sat Nov 07, 2009 6:16 am |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Wed Dec 09, 2009 7:54 am
|
|