Sql server getting data with various intervals

imdx80 · #1 (**permalink**) 03-24-04, 03:14

Hi

I've got a fairly simple table that contains:

Superkey(int), SaveTime(Datetime), TsData(image), SchemaVersion(int), SavedBy(varchar)

The table is indexed by Superkey and Savetime. Superkey specifies which type of record is contained in the TsData field, there are 600+ types of superkey.

Records are created at irregular points throughout the day, some are created every 5 minutes others are created on user input.

These records go back about 4ish years.

My problem is performing a get history request.

the declaration of the stored procedure is

CREATE PROCEDURE GetTsHistory
@Superkey Integer,
@StartDate DateTime,
@EndDate DateTime,
@Interval DateTime

What i need to do is select a number of records between the @startdate and the @enddate depending on the interval. Eg if this is 0 then get everything (which is nice and easy).

The first thing i do is roll the startdate back to the last record prior to the startdate. Eg if you say 10:06 and the last record prior to this point is 10:05 this is your true startpoint. This i do with a

Code:

select @TrueStartTime = max(SaveTime) from TsData where SuperKey=@SuperKey and SaveTime <= @Timestamp

The problem i'm having is what to do when the interval is 1 (A Day) or 0.042 (Hourly) etc... How do i specify that i need to get the first record and then the next record prior to the next interval period?

For example
i have data saved at (only timestamps illustrated)
1. 10:00
2. 10:05
3. 10:10
4. 10:15
5. 10:20
6. 10:26
7. 10:30
8. 10:37
9. 10:42
10. 10:47

and you query for '23 March 2004 10:06', '23 March 2004 10:50', 0.00694 (10 Minutes)

I would expect the recordset to contain records 2,4,5,7 and 9

I'm using sql server 2000

Thanks for any help

imdx80 · #2 (**permalink**) 03-24-04, 05:40

Found a solution which works not sure if its the most elegant solution or if there are any problems with it, if there are hopefully they'll be pointed out fairly quickly (i've not done much directly with databases prior to this project)

the stored procedure FindTsTimestamp takes a date and finds the last timestamp prior to this date
eg if a object is saved every 5 minutes the time 14:47 would return 14:45

Code:

CREATE PROCEDURE GetTsHistory
	@SuperKey Integer,
	@StartDate Datetime,
	@EndDate Datetime,
	@Interval Datetime
AS
	--Find True start time
	declare @TrueStartTime as DateTime
	exec FindTsTimestamp @Superkey,@StartDate,@TrueStartTime OUTPUT

	declare @CurrentTime as DateTime
	select @CurrentTime = @TrueStartTime

	--Create tempory date table
	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Dates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
		drop table [dbo].[tmp_Dates]

	create table tmp_Dates (dt dateTime)

	--Populate date table	
	declare @LastTime as DateTime
	declare @NextTime as DateTime
	select @NextTime = @CurrentTime
	while @CurrentTime < @EndDate
	begin
		if @LastTime != @NextTime
		begin
			insert into tmp_Dates values(@NextTime)
			select @LastTime = @NextTime
		end

		select @CurrentTime = @CurrentTime+@Interval

		exec FindTsTimestamp @SuperKey,@CurrentTime, @NextTime OUTPUT
	end

	--temporary
	--select  * from tmp_dates
	
	--get tsObjects
	select SaveTime, SchemaVersion, SavedBy,TsData from Ts as ts
	inner join tmp_Dates as tmp_Dates on  ts.savetime=tmp_Dates.dt
	where ts.superkey = @Superkey

	drop table tmp_dates
GO

imdx80 · #3 (**permalink**) 03-24-04, 06:23

one improvement changed tmp_Dates table to #tmp_Dates

Internet Services

Web Development

Digital Marketing

Consumer Tech