October 6, 2007 at 10:16 pm
Hi,
I need to generate a report between 2 timestamps in specific intervals.
For e.g. I have to show data for 1 hour in 5 min interval.
StartTime : 2007-10-07 08:23:08.467 (starttime is passed as one of the params with the duration (here 1 hour)
EndTime: 2007-10-07 09:23:08.467
I need to show reults like this
0 2007-10-07 08:23:08.467
0 2007-10-07 08:24:08.467
0 2007-10-07 08:25:08.467
0 2007-10-07 08:26:08.467
0 2007-10-07 08:27:08.467
5 2007-10-07 08:28:08.467
5 2007-10-07 08:29:08.467
5 2007-10-07 08:30:08.467
5 2007-10-07 08:31:08.467
5 2007-10-07 08:32:08.467
.
.
.
.
59
59
I am able to achieve this with the help of a temp table(tmpTable) where i am storing these intervals with starttime and endtime as 2 columns (each with 5 min interval).
tmpStarttime tmpEndtime
0 2007-10-07 08:23:08.467 2007-10-07 08:27:08.467
5 2007-10-07 08:27:08.467 2007-10-07 08:31:08.467
.
.
.
I am passing these 2 timestamps in the query where i am actually fecthing the data.
select tmptable.tmpinterval,table.starttime from
table join tmpTable
on startime between tmpstarttime and tmpendtime
I would like to know is theren any way where i can get this resultset with the help of a query itself rahter thn having a temp table.some set based approach.
Thanks in advance.
PS
October 7, 2007 at 1:30 pm
I assume the lowest interval you want to get is minute.
SELECT DATEDIFF(mi, starttime, endtime) FROM Table
DATEDIFF ( datepart , startdate , enddate )
Returns the number of date and time boundaries crossed between two specified dates.
second = ss
minute = mi
Hour = hh
Both startdate and enddate had to be DATETIME or SMALLDATETIME format.
October 8, 2007 at 12:12 pm
If you have a Number or Tally table, you can generate a list of time values very easily. My Tally table column is Num and the values start at 0. The following will generate a list of 5-minute datetime values starting at starttime and ending at endtime.
[Code]declare @StartTime datetime,
@EndTime datetime;
select @StartTime = '2007-10-07 08:23:08.467',
@EndTime = '2007-10-07 09:23:08.467';
select DateAdd( mi, Num * 5, @StartTime ) as IntervalStart,
DateAdd( mi, (Num+1) * 5, @StartTime ) as IntervalEnd
from Utility..Tally
where DateAdd( mi, Num * 5, @StartTime ) < @EndTime;
[/Code]
You can then join this output with your table of interest and pull out what you want, all in one statement.
[Code]select t.IntervalStart, t.IntervalEnd, d.whatever
from datatable d
join (
select DateAdd( mi, Num * 5, @StartTime ) as IntervalStart,
DateAdd( mi, (Num+1) * 5, @StartTime ) as IntervalEnd
from Utility..Tally
where DateAdd( mi, Num * 5, @StartTime ) < @EndTime
) t
on d.TimeCol between t.IntervalStart and t.IntervalEnd;[/Code]
If you don't have a Tally table, it is just a table with one integer column with sequential values from 0 (or 1) to some arbitrary maximum (mine is 32767. Why 32767? No particular reason. I just like round numbers.:P)).
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply