July 13, 2004 at 11:31 am
I have a loggging table which logs the performance of a benchmark query. I want to write a datediff script that will tell me how many seconds between the 2 most recent log entries:
ID | time | (I log before the start of the query and the end of the query)
13 2004-07-13 13:15:26.140 end query 10 0
12 2004-07-13 13:15:01.263 Start query 0 0
11 2004-07-13 13:13:31.140 end query 10 0
10 2004-07-13 13:13:06.997 Start query 0 0
9 2004-07-13 13:12:19.043 Start query 0 0
8 2004-07-13 13:11:06.140 Start query 0 0
7 2004-07-13 12:54:49.607 Start query 0 0
6 2004-07-13 12:54:34.373 end query 10 0
5 2004-07-13 12:54:06.500 Start query 0 2729
4 2004-07-13 12:41:48.157 end query 10 0
3 2004-07-13 12:41:19.747 Start query 2 0
2 2004-07-13 12:11:23.793 end query 10 0
1 2004-07-13 12:10:59.263 Start query 0 0
July 13, 2004 at 11:56 am
select a.id, a.sysdate, b.id, b.sysdate, a.sysdate - b.sysdate as DiffDate
from test1 as a
inner join (select top 100 percent id, sysdate from Test1 where id < (select count(id) from Test1) order by id desc) as b
on a.id = b.id + 1
order by a.id desc
July 13, 2004 at 11:57 am
This query does as you as. Difference between two most recent. Dont you want the difference between the start and end of each query though? I inserted you data into a table called test.
Create Table Test([field] varchar(45))
Insert Into Test(field)
values('13 2004-07-13 13:15:26.140 end query 10 0')
go
Insert Into Test(field)
values('12 2004-07-13 13:15:01.263 Start query 0 0')
go
Insert Into Test(field)
values('11 2004-07-13 13:13:31.140 end query 10 0')
go
Insert Into Test(field)
values('10 2004-07-13 13:13:06.997 Start query 0 0')
go
Insert Into Test(field)
values('9 2004-07-13 13:12:19.043 Start query 0 0')
go
Insert Into Test(field)
values('8 2004-07-13 13:11:06.140 Start query 0 0')
go
Insert Into Test(field)
values('7 2004-07-13 12:54:49.607 Start query 0 0')
go
Insert Into Test(field)
values('6 2004-07-13 12:54:34.373 end query 10 0')
go
Insert Into Test(field)
values('5 2004-07-13 12:54:06.500 Start query 0 2729')
go
Insert Into Test(field)
values('4 2004-07-13 12:41:48.157 end query 10 0')
go
Insert Into Test(field)
values('3 2004-07-13 12:41:19.747 Start query 2 0')
go
Insert Into Test(field)
values('2 2004-07-13 12:11:23.793 end query 10 0')
go
Insert Into Test(field)
values('1 2004-07-13 12:10:59.263 Start query 0 0')
go
select (select Max(Substring(field, CHARINDEX(' ', field) + 1, CHARINDEX('.', field) - 1)) as datefield
from test) as Field1,
(select Max(Substring(field, CHARINDEX(' ', field) + 1, CHARINDEX('.', field) - 1)) as datefield
from test
where Substring(field, CHARINDEX(' ', field) + 1, CHARINDEX('.', field) - 1)
not in (select Max(Substring(field, CHARINDEX(' ', field) + 1, CHARINDEX('.', field) - 1))
from test)) as field2,
DateDiff(mi, (select Max(Substring(field, CHARINDEX(' ', field) + 1, CHARINDEX('.', field) - 1)) as datefield
from test),
(select Max(Substring(field, CHARINDEX(' ', field) + 1, CHARINDEX('.', field) - 1)) as datefield
from test
where Substring(field, CHARINDEX(' ', field) + 1, CHARINDEX('.', field) - 1)
not in (select Max(Substring(field, CHARINDEX(' ', field) + 1, CHARINDEX('.', field) - 1))
from test)))
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply