Generating Data

I don't know if this is the right place for this question but I'll give it a
shot.
I need to add something to my SQL Server that will allow me to generate a
list of times. What I need to do is supply it with a start an end and an
increment and get back the correct number of rows. For example:
start: 12pm
end: 1pm
increment: 15min
Results:
12:00pm
12:15pm
12:30pm
12:45pm
Any help would be appreciated.Here is a solution. The sequence_number table is just a table of integers
starting at 1.
declare @.start datetime
declare @.end datetime
declare @.increment int
set @.start = '12:00 pm'
set @.end = '1:00 pm'
set @.increment = 15
declare @.numPeriods int
declare @.timeMinutes int
set @.timeMinutes = datediff(minute, @.start, @.end)
set @.numPeriods = @.timeMinutes / @.increment
select convert(varchar(10), dateAdd(minute,(number - 1) *
@.increment,@.start),108)
from sequence_number
where number - 1 <= (@.numPeriods )
This returns:
12:00:00
12:15:00
12:30:00
12:45:00
13:00:00
It will not cross day boundries, but would if you include dates in your time
input.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"eoghain" <eoghain@.discussions.microsoft.com> wrote in message
news:C3DB8497-034D-4836-A46E-416A52D5BF3E@.microsoft.com...
>I don't know if this is the right place for this question but I'll give it
>a
> shot.
> I need to add something to my SQL Server that will allow me to generate a
> list of times. What I need to do is supply it with a start an end and an
> increment and get back the correct number of rows. For example:
> start: 12pm
> end: 1pm
> increment: 15min
> Results:
> 12:00pm
> 12:15pm
> 12:30pm
> 12:45pm
> Any help would be appreciated.|||A Table-Valued User Defined FUnction is what you need:
Create FUNCTION dbo.IncCalendar
(@.StartDT Datetime, @.EndDT DateTime, @.TypeInc VarChar(12), @.Inc Integer)
RETURNS @.Dates Table (DT DateTime)
AS
BEGIN
Declare @.TInc TinyInt
Declare @.Ms TinyInt Set @.Ms = 0
Declare @.Sec TinyInt Set @.Sec = 1
Declare @.Min TinyInt Set @.Min = 2
Declare @.Hr TinyInt Set @.Hr = 3
Declare @.Day TinyInt Set @.Day = 4
Declare @.Wk TinyInt Set @.Wk = 5
Declare @.Mon TinyInt Set @.Mon = 6
Declare @.Qtr TinyInt Set @.Qtr = 7
Declare @.Yr TinyInt Set @.Yr = 8
-- --
If @.typeInc In ('year', 'yy', 'yyyy') Set @.TInc = @.Yr
Else If @.typeInc In ('quarter', 'qq', 'q') Set @.TInc = @.Qtr
Else If @.typeInc In ('month', 'mm', 'm') Set @.TInc = @.Mon
Else If @.typeInc In ('w', 'wk', 'ww') Set @.TInc = @.Wk
Else If @.typeInc In ('dayofyear', 'dy',
'y','day','dd','d') Set @.TInc = @.Day
Else If @.typeInc In ('hour', 'hh') Set @.TInc = @.Hr
Else If @.typeInc In ('minute', 'mi', 'n') Set @.TInc = @.Min
Else If @.typeInc In ('second', 'ss', 's') Set @.TInc = @.Sec
Else If @.typeInc In ('millisecond', 'ms') Set @.TInc = @.Ms
Else Return
Declare @.DT DateTime
Set @.DT = @.StartDT
While @.DT < @.EndDT Begin
Insert @.Dates(DT) Values (@.DT)
If @.TInc = @.Yr Set @.DT = DateAdd(yy, @.Inc, @.DT)
Else If @.TInc = @.Qtr Set @.DT = DateAdd(q, @.Inc, @.DT)
Else If @.TInc = @.Mon Set @.DT = DateAdd(m, @.Inc, @.DT)
Else If @.TInc = @.Wk Set @.DT = DateAdd(wk, @.Inc, @.DT)
Else If @.TInc = @.Day Set @.DT = DateAdd(d, @.Inc, @.DT)
Else If @.TInc = @.Hr Set @.DT = DateAdd(hh, @.Inc, @.DT)
Else If @.TInc = @.Min Set @.DT = DateAdd(n, @.Inc, @.DT)
Else If @.TInc = @.Sec Set @.DT = DateAdd(s, @.Inc, @.DT)
Else If @.TInc = @.Ms Set @.DT = DateAdd(ms, @.Inc, @.DT)
End
RETURN
END
"eoghain" wrote:

> I don't know if this is the right place for this question but I'll give it
a
> shot.
> I need to add something to my SQL Server that will allow me to generate a
> list of times. What I need to do is supply it with a start an end and an
> increment and get back the correct number of rows. For example:
> start: 12pm
> end: 1pm
> increment: 15min
> Results:
> 12:00pm
> 12:15pm
> 12:30pm
> 12:45pm
> Any help would be appreciated.|||Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
use northwind
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a cross join sysobjects as b
go
declare @.sql nvarchar(4000)
declare @.s datetime
declare @.e datetime
declare @.i int
declare @.datepart varchar(15)
set @.s = '2005-01-01T12:00:00.000'
set @.e = '2005-01-01T13:00:00.000'
set @.i = 15
set @.datepart = 'minute'
set @.sql = N'
select
right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
from
number as n
where
number % ' + ltrim(@.i) + N' = 0
and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 126)
+ N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
print @.sql
exec sp_executesql @.sql
go
drop table number
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"eoghain" wrote:

> I don't know if this is the right place for this question but I'll give it
a
> shot.
> I need to add something to my SQL Server that will allow me to generate a
> list of times. What I need to do is supply it with a start an end and an
> increment and get back the correct number of rows. For example:
> start: 12pm
> end: 1pm
> increment: 15min
> Results:
> 12:00pm
> 12:15pm
> 12:30pm
> 12:45pm
> Any help would be appreciated.|||--exec GetTimeList '12PM', '1PM' , 15
create procedure GetTimeList
@.start_time smalldatetime,
@.end_time smalldatetime,
@.minutes int
as
set nocount on
declare @.table table ( time_column varchar(10))
WHILE @.start_time < @.end_time
begin
insert into @.table values( ltrim(right(convert(varchar(50), @.start_time,
100), 7)))
set @.start_time = dateadd(minute, @.minutes, @.start_time )
end
select * from @.table
go
RETURNS:
time_column
--
12:00PM
12:15PM
12:30PM
12:45PM|||Thanks for all the responses. I had already figured out a solution though.
Here is what I did:
CREATE FUNCTION indyme_funcGenerateTimeIncrements(@.start
datetime, @.open
int, @.close int, @.increment int)
RETURNS @.TimeIncrements TABLE
(
EntryDate datetime,
Duration int,
RejectTime int,
ClipTime int
)
AS
BEGIN
DECLARE @.NumRows int
DECLARE @.RowCount int
DECLARE @.DateTime datetime
SET @.NumRows = CEILING(((@.close - @.open) * 60) / @.increment)
IF(@.NumRows = 0)
SET @.NumRows = 1
SET @.RowCount = 0
-- Sets the @.DateTime variable to the startdate at the open time
SET @.DateTime = DATEADD(hh, @.open, CONVERT(datetime, (CONVERT(varchar,
@.start, 101)),101))
WHILE (@.RowCount < @.NumRows)
BEGIN
INSERT @.TimeIncrements
SELECT @.DateTime, 0, 0, 300
SET @.DateTime = DATEADD(n, @.increment, @.DateTime)
SET @.RowCount = @.RowCount + 1
END
RETURN
END
I'm going to look through all of your solutions to see if there is a better
(more efficient) one in there than the one I came up with.
Thanks again.
"eoghain" wrote:

> I don't know if this is the right place for this question but I'll give it
a
> shot.
> I need to add something to my SQL Server that will allow me to generate a
> list of times. What I need to do is supply it with a start an end and an
> increment and get back the correct number of rows. For example:
> start: 12pm
> end: 1pm
> increment: 15min
> Results:
> 12:00pm
> 12:15pm
> 12:30pm
> 12:45pm
> Any help would be appreciated.