Mind the Gap! - SQL Server Forums

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

Transact-SQL (2000)

Mind the Gap!

Author

Topic

ultradiv
Starting Member

41 Posts

Posted - 2008-07-15 : 07:20:59

Hi guys,

I am building a scheduling app.
and it has a table variable with a list of events for a given day.
It contains start and stop times for each event in minutes referenced from 00:00:00 that day. An earliest daystart time is supplied (in this case 500)
I wish to add new events (only duration supplied) but need to insert them into the earliest gap long enough for it. Does that make sense?

table thus (id, start in mins, stop in mins)
1 500 600
2 800 900
3 1200 1500

results after insert of event with duration of 300 mins

1 500 600
(gap of 200 not long enough)
2 800 900
3 900 1200 (new event inserted here)
4 1200 1500

can you help me think of a process that would accomplish this please?

Many thanks
Andy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-15 : 07:25:40

you want the ID also to re-numbered after insertion ? 3 1200 1500 become 4 1200 1500

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

ultradiv
Starting Member

41 Posts

Posted - 2008-07-15 : 07:26:37

quote:
Originally posted by khtan

you want the ID also to re-numbered after insertion ? 3 1200 1500 become 4 1200 1500

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

That would be nice yes please!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-15 : 07:28:18

optional or mandatory ?

is the id always continuous without gap ?

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

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-15 : 07:44:54

Here's what I knocked up...

--data
declare @t table (id int, start int, stop int)
insert @t
          select 1, 500, 600
union all select 2, 800, 900
union all select 3, 1200, 1500

--inputs
declare @duration int
set @duration = 300

--calculation
declare @insertAfterId int, @insertAfterStop int

select top 1 @insertAfterId = a.id, @insertAfterStop = a.stop
from @t a left outer join @t b on a.id = b.id - 1
where isnull(b.start-a.stop, 100000000) >= @duration order by a.id

update @t set id = id + 1 where id > @insertAfterId
insert @t select @insertAfterId + 1, @insertAfterStop, @insertAfterStop + @duration

--results
select * from @t order by id
/*
id          start       stop
----------- ----------- -----------
1           500         600
2           800         900
3           900         1200
4           1200        1500
*/

Note that this needs the ids to be ordered correctly in the first place (and at all times). Is that a fair assumption?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 07:45:36

Hi Ryan!
Nice to have you back.

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-15 : 07:45:54

too late.

Anyway . . . there is my attempt

DECLARE @TABLE TABLE
(
	id		int,
	start		int,
	stop		int
)
INSERT INTO @TABLE
SELECT	1,  500,  600 UNION ALL
SELECT	2,  800,  900 UNION ALL
SELECT	3, 1200, 1500 UNION ALL
SELECT  4, 2000, 2300

INSERT INTO @TABLE (id, start, stop)
SELECT	(SELECT MAX(id) FROM @TABLE) + 1, p.stop, c.start
FROM	@TABLE p
	INNER JOIN @TABLE c	ON	p.id	= c.id - 1
WHERE	c.start - p.stop > 200

UPDATE	t
SET	id	= (SELECT COUNT(*) FROM @TABLE x WHERE x.start <= t.start)
FROM	@TABLE t

SELECT	*
FROM	@TABLE
ORDER BY id
/*
id          start       stop        
----------- ----------- ----------- 
          1         500         600 
          2         800         900 
          3         900        1200 
          4        1200        1500 
          5        1500        2000 
          6        2000        2300 

(6 row(s) affected)
*/

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

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-15 : 07:54:57

And this one ignores the ids...

--data
declare @t table (id int identity(1, 1), start int, stop int)
insert @t
          select 500, 600
union all select 800, 900
union all select 1200, 1500

--inputs
declare @duration int
set @duration = 300

--calculation
declare @newStart int
select top 1 @newStart = a.stop
from @t a left outer join @t b on a.start < b.start
group by a.start, a.stop having isnull(min(b.start) - a.stop, 1000000000) >= @duration order by a.start

insert @t select @newStart, @newStart + @duration

--results
select * from @t order by start
/*
id          start       stop
----------- ----------- -----------
1           500         600
2           800         900
4           900         1200
3           1200        1500
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 07:59:40

Just in case ID is an IDENTITY column

DECLARE	@Sample TABLE (ID INT IDENTITY(1, 1), StartMin INT, StopMin INT)

INSERT	@Sample
SELECT	800,  900 UNION ALL
SELECT	1200, 1500 UNION ALL
SELECT	500,  600

DECLARE	@Duration INT

SET	@Duration = 300

INSERT		@Sample
SELECT TOP 1	s.StopMin,
		s.StopMin + @Duration
FROM		@Sample AS s
WHERE		(SELECT MIN(StartMin) FROM @Sample AS y WHERE y.StartMin > s.StopMin) - s.StopMin >= @Duration
ORDER BY	s.StopMin

SELECT		ID,
		StartMin,
		StopMin
FROM		@Sample
ORDER BY	StartMin

E 12°55'05.25"
N 56°04'39.16"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-15 : 07:59:41

quote:
Originally posted by Peso

Hi Ryan!
Nice to have you back.

E 12°55'05.25"
N 56°04'39.16"

Hi Peso (and khtan) - I would apologise for the sniping if you guys hadn't done it to me so often

It can be really difficult to find an unanswered question to take a break with in those duller moments sometimes...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-15 : 08:04:20

Don't be. It gives us a chance to +1 to my post count

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

ultradiv
Starting Member

41 Posts

Posted - 2008-07-15 : 08:55:38

Hey Guys!!
wow! many thanks I will take a look at your answers in detail.

All I did was go to lunch and pon my return.....

Thanks a million

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources