Calculating Min/Max Timestamp Intervals For Inserted Rows

[BusterKarmul]

Hello,

Is it possible to determine the Min and Max timestamp intervals for records as they are inserted (SQL Server 2005)? The table accepts inserts only (no updates) and produces this output:

[ID] [TxNo]         [UserID]  [TimeStamp]
1    X1866246100    i22222    2011-03-07 05:59:37.863
2    X1884627200    K33333    2011-03-07 06:03:35.740
3    X1878488900    d44444    2011-03-07 06:04:10.520
4    X1885453100    K33333    2011-03-07 06:04:29.097
5    X1878488901    d44444    2011-03-07 06:04:45.503
 

So, for user K33333 I would see the time interval between record ID 2 and ID 4. There are many records besides this for a day, but the table is truncated and archived each evening so there is always one day's worth of data only. So, for this user I would need the minimum and maximum intervals over the course of a day. I've been trying to put my head around it and have this so far, but it is only giving my an overall time lapse in minutes:

        Select Userid, max(TimeStamp) as mTime
        into #Temp1
        from CSDBBRDFC001.Hope.dbo.RealTimeProd
        Where convert(varchar, TimeStamp, 112) = convert(varchar, getdate(), 112)
        Group by UserID
 
        Select a.*, b.FirstName + ' ' + b.LastName as UserName, Datediff(minute, a.mTime, getdate()) as Lapse_Time_in_Minutes
        into #Temp2
        from #Temp1 a LEFT JOIN [User] b
        On upper(a.UserID) = upper(b.ID)
        Order by Datediff(minute, a.mTime, getdate()) Desc
 
        Select * from #Temp2
 

So, as you can see, I'm getting the MAX overall interval, but not the intervals between each insert. Any help with this would be greatly appreciated.

Thanks,
Buster

[Rabbit]

If you need min and max, you can do both in one aggregate query.

[BusterKarmul]

Great, can you please show me an example of how you would do this in one aggregate query?

[Rabbit]

SELECT MIN(FieldName), Max(FieldName) FROM Table1

[BusterKarmul]

OK, I guess I'm not communicating very well. I am trying to get the MIN and MAX intervals between inserts for each user. I know I can get the OVERAL interval (time lapse), but I need the MIN and MAX between EACH insert. So, here would be the output I expect:

userid    tsmax                      tsmin                        tsdiff_min    tsdiff_max
d44444    2011-03-07 06:08:29.097      2011-03-07 06:04:10.520    2            34
i22222    2011-03-07 06:11:29.097      2011-03-07 05:59:37.863    3            23
K33333    2011-03-07 06:11:29.097      2011-03-07 06:03:35.740    1            123
 

Hope this makes sense.

Thanks,
Buster

[Rabbit]

The sample data you have above doesn't match what's in your sample results. Plus you have two fields which look like calculated fields but you haven't specified the formula to calculate them.

[gershwyn]

You should be able to match up each interval between updates using a self-join, doing something like below (assuming the timestamps are in a table called intervals.)

SELECT
  intervals1.userID,
  Max(intervals2.timestamp) AS StartTime,
  intervals1.timestamp AS StopTime
FROM intervals AS intervals1
INNER JOIN intervals AS intervals2
ON intervals1.userID = intervals2.userID
WHERE intervals2.timestamp < intervals1.timestamp
GROUP BY intervals1.userID, intervals1.timestamp
 

This will ignore any entries that have the same timestamp (though it would have to be exactly the same, which seems unlikely to happen) and any userIDs that only have one record.

Now, from there you should be able to calculate the differences between each (are you looking for the difference in minutes?) and use the aggregate functions to pull out min and max.

Is that in the direction you were looking for?

[BusterKarmul]

Whew, this is toughie, not only in terms of sctual development but trying to explain:

What you showed me gives the interval for the first and last inserts, but not all the inserts in-between. I'm thinking I might need a cursor-based solution, although I would always rather prefer a set-based solution. The data that is needed is there in the timestamp intervals. So, for instance here is a select from my table:

[code]
ID TxNo UserID TimeStamp
231228 X1879796000 c333333 2011-03-08 06:00:00.000
231229 X1885506800 c333333 2011-03-08 06:01:00.000
231230 X1887174400 c333333 2011-03-08 06:03:00.000
231231 X1890189900 c333333 2011-03-08 06:05:30.000
[\CODE]

Based on the output above, c333333 has the following inserts beginning at 6:00 am:

231228    X1879796000    c333333    2011-03-08 06:00:00.000
231229    X1885506800    c333333    2011-03-08 06:01:00.000
 

Would have the MIN interval at 1:00.

Then, the following inserts after that:

231231    X1887174400    c333333    2011-03-08 06:03:00.000
231232    X1890189900    c333333    2011-03-08 06:05:30.000
 

Would have the MAX interval of 2:30.

I am looking for the MIN and MAX timestamp intervals between each subsequent insert.

Hope this makes sense.

Thanks,
Buster

[BusterKarmul]

Actually Gershwyn, this is close. I've been able to get teh interval for each insert (MAX(DATEPART(minute,intervals2.timestamp)) - DATEPART(minute,intervals1.timestamp) AS Interval) and am trying to figure out how to get the MIN and MAX of this. I think I'm close and really appreciate your help.