days between dates from a list

Hi,
I am trying to perform an interpolation of counts between event dates...my
data looks like this:
Event Date Count
1/1/06 13
1/17/06 9
2/3/06 7 etc...
The spacing of event date is not always equal thus I need to be able to do
something like this: (date1-nextdate). I don't know how to select the next
date. Any help if greatly appreciated.
Jen...learning
This might work and be fast if event date is a PK or indexed:
SELECT
E.[EventDate], E.[CountOfThings], dbo.ufn_NextEvent(E.[EventDate]) AS
NextDate
FROM
Events E
Where dbo.ufn_NextEvent is a user defined function like:
CREATE FUNCTION [dbo].[ufn_NextEvent]
(
@.ThisEvent DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @.result DATETIME
SELECT TOP 1 @.result = [EventDate] FROM Events WHERE [EventDate] > @.ThisEvent
RETURN (@.result)
END
Result set is:
2006-01-01 00:00:00.000132006-01-17 00:00:00.000
2006-01-17 00:00:00.00092006-02-03 00:00:00.000
2006-02-03 00:00:00.0007NULL
Regards,
JayAchTee
"jennifer.heintz" wrote:

> Hi,
> I am trying to perform an interpolation of counts between event dates...my
> data looks like this:
> Event Date Count
> 1/1/06 13
> 1/17/06 9
> 2/3/06 7 etc...
> The spacing of event date is not always equal thus I need to be able to do
> something like this: (date1-nextdate). I don't know how to select the next
> date. Any help if greatly appreciated.
> --
> Jen...learning