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

DateDiff That Only Counts Working Days

Hi! I'm trying to create a query to calculate the number of days between two dates, but I only want to include working days. Is there a way to do this?Most DBAs work seven days a week, weekends and holidays included ;). The provided DateDiff works fine for us!

On a (very slightly) more serious note, what constitutes a "working day" by your definition?

-PatP|||LOL, I definitely realize what days DBAs work... :)

Working days are just the typical Monday through Friday.|||Vacations? Holidays? Snow days? Office closed due to threat of terrorist attack? Those all affect "working days". Or did you mean to ask "I'm trying to create a query to calculate the number of weekdays between two dates, excluding weekends."?|||Would you include Holidays as work days?

IF not I would suggest you have a table calendar with field workday
values 1 , 0

sum the workday field to get your answer.|||Yes, this is what I'm trying to say:

"I'm trying to create a query to calculate the number of weekdays between
two dates, excluding weekends."|||http://www.aspfaq.com/show.asp?id=2453