How can i generate timeslots on sql?

Question

this is what i want to work with (shortened)

create slots table:
start time
end time
duration

slots list table:
timeslot

^ all the above is in datetime except for duration which is int

what i want is creating timeslots on another table e.g:
start time - 2010-01-01 10:00:00.000
end time - 2010-01-01 11:00:00.000
duration - 10 (in mins)

generating:

timeslot
-2010-01-01 10:00:00.000
-2010-01-01 10:10:00.000
-2010-01-01 10:20:00.000
-2010-01-01 10:30:00.000
-2010-01-01 10:40:00.000
-2010-01-01 10:50:00.000
2010-01-01 11:00:00.000

iam sorry about this but i have 0% clue on coding

also jw what is needed for the code to work automatically without me running it.



iam using sql server 2008 any help appreciated thanks

Answer 1

Since you're using SQL Server, define your columns as SMALLDATETIME, which only keeps the time portions as hour/minute. Then, you could create a procedure to insert rows into timeslot like:



CREATE PROCEDURE makeSlots (@startTime AS SMALLDATETIME, @endTime AS SMALLDATETIME, @minutesDuration AS int)

RETURNS int

AS

BEGIN

DECLARE @thisTime SMALLDATETIME = @startTime;

DECLARE @insertCount int = 0;

WHILE @thisTime <= @endTime

BEGIN

INSERT INTO timeslot VALUES (@thisTime);

@insertCount = @insertCount +1;

@thisTime = DATEADD(mi, @minutesDuration, @thisTime)

END;

RETURN @insertCount;

END;

Answer 2

The answer to both questions is "in an external program, written in some programming language".

Answer 3

Try this:

-----------

with cte

as(select starttime, starttime nexttime,endtime,duration from slots

union all

select starttime, DATEADD(MINUTE,duration,nexttime), endtime,duration from cte

where nexttime < endtime)

select convert(varchar(16),starttime,120), convert(varchar(16),nexttime, 120) from cte;

-----------