T-SQL: Difference in values of a column in two rows

[MM2001]

Current Data Set:

MYID ACTIVITY START_TIME REASON
--------- -------- ------------ ------
5610 Log On 0000
5610 Sign Off 0100 Break1
5610 Sign On 0300
5610 Sign Off 0600 Break2
5610 Sign On 0900
5610 Log Off 1900

Expected Output:

MYID ACTIVITY START_TIME REASON TOTAL_Time
--------- -------- ------------ ------ ------------
5610 Log On 0000
5610 Sign Off 0100 Break1
5610 Sign On 0300 200
5610 Sign Off 0600 Break2
5610 Sign On 0900 300
5610 Log Off 1900

In Oralce, I can use DECODE and LAG function to achieve the above results.

SELECT myid, activity, start_time, reason,
(start_time - DECODE (activity, 'Sign On', LAG (start_time) OVER
(ORDER BY start_time), '')) total
FROM work_log
/

In SQL Server 2000, I can take advantage of CASE to replace DECODE and not sure
whether we have an equivalent of LAG.
SELECT myid, activity, sttime, reason,
(sttime - (case activity
when 'Sign On' then dbo.MyLag_FN(sttime)
end)) total
FROM work_log
go

Any input/solution to my problem will be appreciated. Thanks

MM





if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[Work_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Work_Log]
GO

CREATE TABLE [dbo].[Work_Log] (
[MyID] [int] NULL ,
[Activity] [varchar] (10) NULL ,
[StTime] [varchar] (4) NULL ,
[Reason] [varchar] (10) NULL
) ON [PRIMARY]
GO
insert into work_log values (5610, 'Log On', '0000', '')
go
insert into work_log values (5610, 'Sign Off', '0100', 'Break1')
go
insert into work_log values (5610, 'Sign On', '0300', '')
go
insert into work_log values (5610, 'Sign Off', '0600', 'Break2')
go
insert into work_log values (5610, 'Sign On', '0900', '')
go
insert into work_log values (5610, 'Log Off', '1900', '')
go