| Author |
 Topic  |
|
|
sureshsmanian
Starting Member
24 Posts |
 Posted - 05/16/2012 : 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
Sweden
29156 Posts |
Posted - 05/16/2012 : 05:50:08
|
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
N 56°04'39.26"
E 12°55'05.63" |
 |
|
|
sureshsmanian
Starting Member
24 Posts |
Posted - 05/16/2012 : 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;
--------------------- |
|
|
| |
Topic |
|
|
|
create Time slots in sql server 2008
