how to count record value based on previous record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to count record value based on previous record

Author

Topic

szabonorbert
Starting Member

2 Posts

Posted - 2006-06-30 : 04:01:18

Hello!

I'm using MS SQL Server 2000.
I would like to make a query (not stored procedure) where I can count a record value from the previous(or next) record value.

/Table Event/
EventID StartDate
------------------------------
2 2006-06-20 13:30:10
4 2006-06-20 13:30:46
11 2006-06-20 13:31:23
5 2006-06-20 13:32:03
4 2006-06-20 13:32:14
...
so, i would like to show three fields, eventid,startdate, and the difference (in seconds) between each event. i cannot use stored procedures. is it possible to reach other records value?

thanks for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 04:33:26

[code]declare @event table (EventID INT, StartDate datetime)

insert @event
select 2, '2006-06-20 13:30:10' union all
select 4, '2006-06-20 13:30:46' union all
select 11, '2006-06-20 13:31:23' union all
select 5, '2006-06-20 13:32:03' union all
select 4, '2006-06-20 13:32:14'

select e.EventID,
e.StartDate,
DATEDIFF(ss, e.StartDate, (SELECT ISNULL(MIN(StartDate), GETDATE()) FROM @event where StartDate > e.StartDate)) Secs
from @event e
order by e.StartDate,
e.EventID[/code]Output[code]ID StartDate Secs
-- ----------------------- ------
2 2006-06-20 13:30:10.000 36
4 2006-06-20 13:30:46.000 37
11 2006-06-20 13:31:23.000 40
5 2006-06-20 13:32:03.000 11
4 2006-06-20 13:32:14.000 853148[/code]
Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-30 : 04:55:02

I am not sure, but it looks to me that szabonorbert wanted the other way round.

Modify from Peter's code

declare @event table (EventID INT, StartDate datetime)

insert @event
select  2, '2006-06-20 13:30:10' union all
select  4, '2006-06-20 13:30:46' union all
select 11, '2006-06-20 13:31:23' union all
select  5, '2006-06-20 13:32:03' union all
select  4, '2006-06-20 13:32:14'

select		e.EventID,
		e.StartDate,
		DATEDIFF(ss, (SELECT ISNULL(MAX(StartDate), e.StartDate) FROM @event x where x.StartDate < e.StartDate), e.StartDate) Secs
from		@event e
order by	e.StartDate,
		e.EventID

/* RESULT

EventID     StartDate                                              Secs        
----------- ------------------------------------------------------ ----------- 
2           2006-06-20 13:30:10.000                                0
4           2006-06-20 13:30:46.000                                36
11          2006-06-20 13:31:23.000                                37
5           2006-06-20 13:32:03.000                                40
4           2006-06-20 13:32:14.000                                11

*/