Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Sql server getting data with various intervals

    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

  2. #2
    Join Date
    Mar 2004
    Posts
    4

    A Solution?

    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

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    one improvement changed tmp_Dates table to #tmp_Dates

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •