create Time slots in sql server 2008

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

General SQL Server Forums

New to SQL Server Programming

create Time slots in sql server 2008

Author

Topic

sureshsmanian
Starting Member

31 Posts

Posted - 2012-05-16 : 04:38:39

Hi all,
I would like to create the timeslots in the following manner:

StartTime | EndTime | Slot
---------------------------
9:00 9:20 1
9:20 9:40 2
9:40 10:00 3
. . 4
. . 5
. . 6

-------------------------
I have created the Scalar function (given below),

-----------------------
Function
---------
create FUNCTION dbo.TimeTable
(
@StartTime time,
@EndTime time,
@slot int=0
)

RETURNS @tt TABLE
(
StartTime time,
EndTime time,
slot int
)

as
begin
;With tt_CTE(stTime,endTime,slot)
AS
(
Select @StartTime,DateAdd(mi,20,@StartTime),@slot+1
union all
Select stTime,DateAdd(mi,20,@StartTime),@slot+1
from tt_CTE
where DateAdd(mi,20,@StartTime) <= @EndTime
)

INSERT INTO @tt
SELECT stTime,endTime,slot
FROM tt_CTE
OPTION (MAXRECURSION 0)

RETURN
END

-------------------
Function call
-------------------

use test;
Go
declare @tt table(StartTime time, EndTime time, slot int)

Insert into @tt
select StartTime,EndTime,slot from dbo.TimeTable('09:00:00','10:00:00',0);

select * from @tt;

------------------------

When I execute the function the query gets executed unconditionally without terminating.Please help me. Thanks for your help.

Regards
Krish.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-16 : 05:50:08

[code]DECLARE @FromTime TIME = '09:00:00',
@ToTime TIME = '10:00:00',
@Interval TINYINT = 20

SELECT DATEADD(MINUTE, @Interval * Number, @FromTime) AS StartTime,
DATEADD(MINUTE, @Interval + @Interval * Number, @FromTime) AS EndTime,
1 + Number AS Slot
FROM master.dbo.spt_values
WHERE [Type] = 'P'
AND Number BETWEEN 0 AND (DATEDIFF(MINUTE, @FromTime, @ToTime) - 1) / @Interval[/code]

N 56°04'39.26"
E 12°55'05.63"

sureshsmanian
Starting Member

31 Posts

Posted - 2012-05-16 : 06:22:44

@SwePeso
Thanks for your reply and its working fine.
I have made the simple while loop to execute the same instead of using recursion in my question.

-------------

create FUNCTION dbo.TimeTable1
(
@StartTime time,
@EndTime time,
@slot int
)

RETURNS @tt TABLE
(
StartTime time,
EndTime time,
slot int
)

as

begin

while @StartTime <= @EndTime
begin
insert into @tt
select @StartTime,convert(Time,DateAdd(mi,20,@StartTime)),@slot;

set @StartTime = DateAdd(mi,20,@StartTime)
set @slot = @slot + 1

end;
RETURN
END
-----------------

use test;
Go
declare @tt table(StartTime time, EndTime time, slot int)

Insert into @tt
select StartTime,EndTime,slot from dbo.TimeTable1('09:00:00','16:00:00',1);

select * from @tt;

---------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources