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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy