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

DateTime formating.

I am trying to query a calendar table that has the [start] datetime of an event and then a [title] of the event. I am trying to display them as;

* 8:00 AM Get to work
* Independance day

some of the events have a start time and some only have a date. the ones that do not have a start time have a start time of 12:00:00 How do I query the two fields and only show the start time & title of the events that have a start time and just the title for the ones that do not have a start time..

I've tried this...

selectEvent=(CONVERT(CHAR(8),start,8)+' '+ title)fromCalendarwhereCONVERT(CHAR(8),start,8)<>'12:00:00'

It does display the start and event title but only for the ones that have a start time. And how do I get the 8:00 AM format for the time?

Thanks for any help.

Mark

Perhaps something like this might work:

SELECTCASEWHEN(datevalisNOTNULLAND dateval!='')THEN REPLACE(REPLACE(RIGHT(CONVERT(varchar,dateval,100),7),'P',' P'),'A',' A')ELSE''END +' ' + titleFROM yourTableWHERE

|||

--Try somthing like this:

SELECTCASEWHENCONVERT(varchar(8),date,108)='12:00:00'THEN'*'+title --Or CONVERT(varchar(8),date,108)='00:00:00'

ELSE'*'+RIGHT(CONVERT(varchar(19),date,100), 7)+' '+ title

ENDASEvent

FROM calendarwithTitle

|||Perfect thanks alot that took care of it for me!