Calculating Min/Max Timestamp Intervals (SQL 2005) 1

[larrydavid]

Hello,

Is it possible to determine the Min and Max timestamp intervals for records as they are inserted? 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. Am I on the right track? Any help with this would be greatly appreciated as always.

Thanks,
Larry

 

[simian336]

Something like....

/*
create table ts (
ID int,
TxNo varchar(20),
UserID char(6),
[TimeStamp] datetime)
*/

insert into ts values (1,'X1866246100','i22222','2011-03-07 05:59:37.863')
insert into ts values (2,'X1884627200','K33333','2011-03-07 06:03:35.740')
insert into ts values (3,'X1878488900','d44444','2011-03-07 06:04:10.520')
insert into ts values (4,'X1885453100','K33333','2011-03-07 06:04:29.097')
insert into ts values (5,'X1878488901','d44444','2011-03-07 06:04:45.503')

with tmax as (
select userid, Max(timestamp) as tsmax
from ts
group by userid
),
tmin as
(
select userid, Min(timestamp) as tsmin
from ts as s
group by userid
)
Select tmax.userid, tmax.tsmax, tmin.tsmin, datediff(ss, tmin.tsmin, tmax.tsmax) as tsdiff
from tmax
join tmin
on tmax.userid= tmin.userid


Simi

 

[larrydavid]

HI Simi,

Thank you very much for your query. I ran it and understand what you are doing, but still cannot seem to get the min and max intervals between inserts for a particular user. In other words, here is the output from your query after inserting 10 test values:

insert into ts values (1,'X1866246100','i22222','2011-03-07 05:59:37.863')
insert into ts values (2,'X1884627200','K33333','2011-03-07 06:03:35.740')
insert into ts values (3,'X1878488900','d44444','2011-03-07 06:04:10.520')
insert into ts values (4,'X1885453100','K33333','2011-03-07 06:05:29.097')
insert into ts values (5,'X1878488901','d44444','2011-03-07 06:06:45.503')
insert into ts values (6,'X1885453100','K33333','2011-03-07 06:07:29.097')
insert into ts values (7,'X1885453100','d44444','2011-03-07 06:08:29.097')
insert into ts values (8,'X1885453100','K33333','2011-03-07 06:11:29.097')
insert into ts values (9,'X1885453100','i22222','2011-03-07 06:11:29.097')
insert into ts values (10,'X1885453100','K33333','2011-03-07 06:11:29.097')

userid	tsmax	                  tsmin	                tsdiff
d44444	2011-03-07 06:08:29.097	2011-03-07 06:04:10.520	259
i22222	2011-03-07 06:11:29.097	2011-03-07 05:59:37.863	712
K33333	2011-03-07 06:11:29.097	2011-03-07 06:03:35.740	474

But what I'm trying to get is the MIN tsdiff and MAX tsdiff, so this is very close I think. I've been trying different things but cannot see it.

Thanks,
Larry

 

[larrydavid]

Here is the output I am looking for:

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

Thanks,
Larry

 

[simian336]

So if I understand correctly you have multiple login's and outs.
You want to know compare diffferences between length of time it took to log in and out time.

If so, you have one magor problem. Your data does not really indicate which is a log in our a log out. For example in your data about 'K33333' is listed 5 times.

Simi

 

[larrydavid]

Hi Simi,

Not a login or log out, the time interval between each insert. 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:

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

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,
Larry

 

[sparkbyte]

I am sorry, but would that not be 4 inserts and 3 intervals?
Not 4 inserts and 2 intervals as you are explaining above.


The 4 rows for c333333
[tt]
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
[/tt]

1st Time Interval ( 1:00 )
[tt]
ID TxNo UserID TimeStamp
-----------------------------------------------------------
231228 X1879796000 c333333 2011-03-08 06:00:00.000
231229 X1885506800 c333333 2011-03-08 06:01:00.000
[/tt]

2nd time Interval ( 2:00 )
[tt]
ID TxNo UserID TimeStamp
-----------------------------------------------------------
231229 X1885506800 c333333 2011-03-08 06:01:00.000
231230 X1887174400 c333333 2011-03-08 06:03:00.000
[/tt]

3rd Time Interval ( 2:00 )
[tt]
ID TxNo UserID TimeStamp
-----------------------------------------------------------
231230 X1887174400 c333333 2011-03-08 06:03:00.000
231231 X1890189900 c333333 2011-03-08 06:05:30.000
[/tt]



Expected Output??
[tt]
userid tsmax tsmin tsdiff_min tsdiff_max
(IDs used for tsDiff calc) (IDs used for tsDiff calc)
------------------------------------------------------------------------------------------
c333333 (23230 - 231231) (23228 - 231229) 1:00 2:00
[/tt]


Would this not be more correct?
I am not sure how to get the output to look like this, I was just including the IDs
for an example so that we are understanding what you are needing.

Thanks

John Fuhrman

 

[SQLBill]

Or would the MAX be 2:30 based on ids 231230 and 231231?

The only way I see to do this would be to put each interval (per userid) into a temp table and then pull the min and max from that table.

So you would have a table with

C333333 1:00
c333333 2:00
c333333 2:30

Then pulling min and max from that would give you the first and last row.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[sparkbyte]

Your right Bill. Can't add/subtract times by hand apparently....

1:00
2:00
2:30

Would have been correct in my reply, Sorry.

Thanks

John Fuhrman

 

[gmmastros]

I think this is the query you're looking for:

Declare @ts Table(ID Int, TxNo VarChar(20), UserId Char(7), TimeStamp DateTime)

insert into @ts values (1,'X1866246100','i22222','2011-03-07 05:59:37.863')
insert into @ts values (2,'X1884627200','K33333','2011-03-07 06:03:35.740')
insert into @ts values (3,'X1878488900','d44444','2011-03-07 06:04:10.520')
insert into @ts values (4,'X1885453100','K33333','2011-03-07 06:05:29.097')
insert into @ts values (5,'X1878488901','d44444','2011-03-07 06:06:45.503')
insert into @ts values (6,'X1885453100','K33333','2011-03-07 06:07:29.097')
insert into @ts values (7,'X1885453100','d44444','2011-03-07 06:08:29.097')
insert into @ts values (8,'X1885453100','K33333','2011-03-07 06:11:29.097')
insert into @ts values (9,'X1885453100','i22222','2011-03-07 06:11:29.097')
insert into @ts values (231228,'X1879796000','c333333','2011-03-08 06:00:00.000')
insert into @ts values (231229,'X1885506800','c333333','2011-03-08 06:01:00.000')
insert into @ts values (231230,'X1887174400','c333333','2011-03-08 06:03:00.000')
insert into @ts values (231231,'X1890189900','c333333','2011-03-08 06:05:30.000')

Select * From @ts Where UserId = 'k33333'

; With RankingData As
(
  Select  TxNo, UserId, [TimeStamp],
          Row_Number() Over (Partition By UserId Order By [TimeStamp]) As RowId
  From    @ts
)
Select A.UserId, 
       Max(B.TimeStamp) As tsmax,
       Min(A.TimeStamp) As tsmin,
       DateAdd(Second, Min(DateDiff(Second, A.TimeStamp, B.TimeStamp)), 0) as tsdiff_min, 
       DateAdd(Second, Max(DateDiff(Second, A.TimeStamp, B.TimeStamp)), 0) As tsdiff_max
From   RankingData As A
       Inner Join RankingData As B
         On A.UserId = B.UserId
         And A.RowId = B.RowId - 1
Group BY A.UserId

If this returns the correct data and you would like for me to explain it, just let me know.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Just a minor tweak, stripped off the date portion of tsdiff_min and tsdiff_max so that it only shows Hours/Min/Sec.

Select A.UserId, 
		Max(B.TimeStamp) As tsmax,
		Min(A.TimeStamp) As tsmin,
		CONVERT(VARCHAR(8),DateAdd(Second, Min(DateDiff(Second, A.TimeStamp, B.TimeStamp)), 0),108) as tsdiff_min, 
		CONVERT(VARCHAR(8),DateAdd(Second, Max(DateDiff(Second, A.TimeStamp, B.TimeStamp)), 0),108) As tsdiff_max
From   RankingData As A
       Inner Join RankingData As B
         On A.UserId = B.UserId
         And A.RowId = B.RowId - 1
Group BY A.UserId

BTW, thanks george for the great example of ranking. A star!!

Thanks

John Fuhrman

 

[mptwoadmin]

Hi,
I would like to ask a question. How would you change the QRY to accomplish the following with the data provided?

The 4 rows for c333333

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


UserID TimeStamp Time
c333333 2011-03-08 06:00:00.000 - 2011-03-08 06:01:00.000 04:00
c333333 2011-03-08 06:03:00.000 - 2011-03-08 06:05:30.000 02:30

 

[markros]

;with cte as (select *, row_number() over (partition by UserID order by TimeStamp) as Row from myTable)

select c1.UserID, c1.TimeStamp, c2.TimeStamp as [Time]
from cte c1 LEFT JOIN cte c2 on c1.UserID = c2.UserID
and c1.Row = c2.Row - 1 and c1.Row %2 = 1

from the top of my head.

PluralSight Learning Library