Calculating timespans between access log entries

Try,
select
a.changelog_id,
a.username,
a.[action],
a.changedate,
a.recordid,
b.changelog_id,
b.[action],
b.changedate,
b.recordid,
datediff(second, a.changedate, b.changedate) as [timespam]
from
#ChangeLog as a
inner join
#ChangeLog as b
on a.[action] = 'ACCESS'
and b.[action] = 'UPDATE'
and a.recordid = b.recordid
and a.changedate = (
select max(c.changedate)
from #ChangeLog as c
where c.recordid = b.recordid and c.changedate < b.changedate
)
go
AMB
"David D Webb" wrote:

> I have a log table that records when certain records are accessed and
> updated. Now someone wants a report that gives the timespan between when
> the record was accessed, and when it was updated. There can be multiple
> ACCESS entries without an UPDATE, but there will always be an ACCESS
> preceding an UPDATE. Anyway to do this in a set based solution. They
> realize the data is not perfect, nor will the results be, but are looking
> for rough usage counts.
> The report from the following data should return 3 lines with:
> username, seconds, recordid
> It should only look at UPDATE records and the ACCESS record immediately
> preceding it.
> CREATE TABLE #ChangeLog (
> [changelog_id] [int] IDENTITY (1, 1) NOT NULL,
> [username] [varchar] (50) NOT NULL,
> [action] [varchar] (50) NOT NULL,
> [changedate] [datetime] NOT NULL,
> [recordid] [int] NOT NULL
> )
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 1:41:00 PM','hareyj','ACCESS',1224)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 1:59:29 PM','hareyj','UPDATE',1224)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 2:12:00 PM','jsmith','ACCESS',4233)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 2:15:00 PM','jsmith','ACCESS',9887)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 2:25:55 PM','jsmith','UPDATE',9887)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 4:04:00 PM','hareyj','ACCESS',3432)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 4:14:00 PM','hareyj','ACCESS',3432)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 4:23:00 PM','hareyj','ACCESS',3432)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 4:44:21 PM','hareyj','UPDATE',3432)
> SELECT * FROM #ChangeLog
>
> Thanks,
> Dave
>
>Thanks Alejandro! I didn't think of doing the self join like that.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5F9F645E-79FA-4D75-B53C-FA05466D3043@.microsoft.com...
> Try,
> select
> a.changelog_id,
> a.username,
> a.[action],
> a.changedate,
> a.recordid,
> b.changelog_id,
> b.[action],
> b.changedate,
> b.recordid,
> datediff(second, a.changedate, b.changedate) as [timespam]
> from
> #ChangeLog as a
> inner join
> #ChangeLog as b
> on a.[action] = 'ACCESS'
> and b.[action] = 'UPDATE'
> and a.recordid = b.recordid
> and a.changedate = (
> select max(c.changedate)
> from #ChangeLog as c
> where c.recordid = b.recordid and c.changedate < b.changedate
> )
> go
>
> AMB
>
> "David D Webb" wrote:
>