Hello All,
First off thanks to all who try to help me.
I have a table with a date timestamp that includes the minute in 24 hour format..
I need to return all the records in the date range between '1/1/2007' and '1/31/2007' which is very simple.
The part I am having a hard time with is in the where clause.
I retrieve all the records for the date range into a #temptable
Now I need to sort these into 15 minute intervals based on the timestamp, but the sproc does'nt get passed a time only two dates also I need to provide a count of the records returned.
I have tried to create a temp table with 96 records being that there are 96 intervals of 15 minutes in 24 hours.
and using that as kinda a join.
I have tried to do something with datediff(_) with a dateadd(_)+15 in the where clause.
I also tried a loop 96 times.
I know there are a few ways to do it.I just dont know which ones.
I've only been using tsql for 2 months and have come pretty far, but this situation evades me.
Please enlighten me with your infinite wisdom of the tsql language. Help !
Is this the idea?
declare @.sampleData table
( rid integer primary key,
sampleDate datetime
)
insert into @.sampleData
select iter,
cast ('12/25/6' as datetime) + 70 * dbo.rand()
from small_iterator (nolock) -- A table of integers 1-32767
select top 5
rid,
sampleDate,
p.startOfInterval,
p.endOfInterval
from @.sampleData a
inner join
( select iter,
cast ((iter-1) * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as startOfInterval,
cast (iter * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as endOfInterval
from small_iterator (nolock) -- a table of integers 1-32767
where iter <= 96
) p
on sampleDate >= '1/1/7'
and sampleDate < '2/1/7'
and cast(cast(sampleDate as binary(4)) as datetime) >= p.startOfInterval
and cast(cast(sampleDate as binary(4)) as datetime) < p.endOfInterval
order by rid
-- rid sampleDate startOfInterval endOfInterval
-- -- - -
-- 1 2007-01-02 14:58:52.183 1900-01-01 14:44:59.997 1900-01-01 15:00:00.000
-- 3 2007-01-30 08:00:32.787 1900-01-01 08:00:00.000 1900-01-01 08:15:00.000
-- 4 2007-01-16 19:59:58.257 1900-01-01 19:45:00.000 1900-01-01 19:59:59.997
-- 5 2007-01-04 10:34:13.883 1900-01-01 10:30:00.000 1900-01-01 10:44:59.997
-- 8 2007-01-22 08:14:12.333 1900-01-01 08:00:00.000 1900-01-01 08:15:00.000
select left(convert (varchar(8), p.startOfInterval, 108), 5) as startOfInterval,
left(convert (varchar(8), p.endOfInterval, 108), 5) as endOfInterval,
count(*) as intervalCount
from @.sampleData a
inner join
( select iter,
cast ((iter-1) * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as startOfInterval,
cast (iter * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as endOfInterval
from small_iterator (nolock) -- a table of integers 1-32767
where iter <= 96
) p
on sampleDate >= '1/1/7'
and sampleDate < '2/1/7'
and cast(cast(sampleDate as binary(4)) as datetime) >= p.startOfInterval
and cast(cast(sampleDate as binary(4)) as datetime) < p.endOfInterval
group by p.startOfInterval, p.endOfInterval
order by p.StartOfInterval
-- startOfInterval endOfInterval intervalCount
-- - -
-- 00:00 00:15 151
-- 00:15 00:30 158
-- 00:30 00:45 134
-- ...
-- 23:30 23:44 145
-- 23:44 00:00 158
|||
Getting Closer..
Thank you very much Kent.
I wish I could write code like that in Tsql and actually "Get it to work" for me.
I need the data to look like this which for the most part the above does.
Date 1/1/2007 <Whatever user chooses
Time Interval Number Calls
12:00 12:15 147
12:15 12:30 117
12:30 12:45 215
The existing data I have looks like this for the timestamp field.
12/22/2003 9:29:00 AM
1/5/2004 1:31:00 PM
7/2/2003 3:53:00 PM
7/7/2003 1:27:00 PM
I have thought about parsing out the strings then back converting them but with sql we shouldnt have to do that.
I'm going to attempt to plug the above code in and see what I can do with it. More suggestions are welcome !
|||Do you need me to post my dbo.rand() function and small_iterator table?|||
You could change the SELECT list to that shown below.
Displaying the date above the output results (i.e. handling presentation) isn't what SQL Server is good at. It's far better to handle this in the client code or in Reporting Services.
Chris
SELECT CAST(DATEPART(hh, p.startOfInterval) AS VARCHAR(2)) + ':'
+ CAST(DATEPART(mi, p.startOfInterval) AS VARCHAR(2)) + ' '
+ CAST(DATEPART(hh, e.endOfInterval) AS VARCHAR(2)) + ':'
+ CAST(DATEPART(mi, e.endOfInterval) AS VARCHAR(2)) AS [Time Interval],
COUNT(*) AS [Number Calls]
|||
Kent - I know you weren't talking to me, but it would be good if you could. I've noticed you use small_iterator in a number of your posts.
Is it just a table of sequential integers?
Cheers
Chris
|||
Yes, small_iterator is a list of integers 1-32767; stand by and I will post this stuff. Here is another shot at your summary (the SMALL_ITERATOR and dbo.rand() will follow in a minute):
declare @.searchDate datetime
set @.searchDate = '3/1/7'
declare @.sampleData table
( rid integer primary key,
sampleDate datetime
)
insert into @.sampleData
select iter,
cast ('12/25/6' as datetime) + 70 * dbo.rand()
from small_iterator (nolock) -- A table of integers 1-32767
select left(convert (varchar(8), p.startOfInterval, 108), 5) + ' ' +
left(convert (varchar(8), p.endOfInterval, 108), 5) + ' ' as [ Time Interval],
count(*) as intervalCount
from @.sampleData a
inner join
( select iter,
cast ((iter-1) * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as startOfInterval,
cast (iter * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as endOfInterval
from small_iterator (nolock) -- a table of integers 1-32767
where iter <= 96
) p
on sampleDate >= @.searchDate
and sampleDate < (@.searchDate + 1)
and cast(cast(sampleDate as binary(4)) as datetime) >= p.startOfInterval
and cast(cast(sampleDate as binary(4)) as datetime) < p.endOfInterval
group by p.startOfInterval, p.endOfInterval
order by p.StartOfInterval
-- Time Interval intervalCount
-- -- -
-- 00:00 00:15 4
-- 00:15 00:30 6
-- 00:30 00:45 6
-- ...
-- 23:30 23:44 2
-- 23:44 00:00 2
|||
Here is my small_iterator table; it is simply a table of numbers. To get some ideas about tables of numbers, give this website a look:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)
go
/* -- */
/* This routine is used to populate the small_iterator table. */
/* This query ran in 1 second in development and should run at */
/* a similar speed in production. */
/* -- */
truncate table SMALL_ITERATOR
insert into small_iterator
select number from master.dbo.spt_values (nolock)
where name is null
and number <= 255
insert into small_iterator
select 256 * j.iter + i.iter
from small_iterator i
inner join small_iterator j
on j.iter > 0
and j.iter <= 127
order by 256 * j.iter + i.iter
delete from small_iterator where iter = 0
select count(*) [count],
min (iter) [min iterator],
max (iter) [max iterator]
from SMALL_ITERATOR
go
dbcc dbreindex (small_iterator, '', 100)
go
update statistics small_iterator
go
exec sp_recompile small_iterator
go
|||
Here is my RAND scalar UDF; it comes in handy at times for generating mock data:
create view dbo.vRand
as
select rand () as vRand
go
create function dbo.rand ()
returns float
as
begin
return (select vRand from dbo.vRand)
end
go
create function dbo.randList
( @.pm_listSize integer
)
returns @.randList table
( rid integer,
iRand float
)
as
begin
declare @.upperBound integer
set @.upperBound = ceiling (convert(float, (@.pm_listSize+1))
/ convert (float, 32767))
insert into @.randList
select 32767*(j.iter-1) + i.iter - 1 as rid,
dbo.rand() as iRand
from small_iterator i (nolock)
inner join small_iterator j (nolock)
on j.iter <= @.upperBound
and 32767*(j.iter-1) + i.iter - 1 <= @.pm_listSize
and 32767*(j.iter-1) + i.iter - 1 > 0
return
end
go
|||
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=81913&SiteID=1
declare @.interval int
set @.interval=30
select dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0) [dt],
count(*) [cnt]
from Northwind..Orders
group by dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0)
order by 1
This is looking like it's going to work for my needs just have to tweak it a bit more..
Thanks to all who helped. One day I'll be able to help with tsql instead of just VB and ASP
