I am unable to write the row number function and I could not know how to compare the two rows...

please help me with an idea what to use...

The resulted output should be like this....

ChangeIDrefOTSIDOttimeOldTypeOldlocalNewTypeNewlocalDateChangedDateCloseddifference

77,39748,242Oct 29, 2010 11:50:00 AMDecision10/30/2010 6:00:00 AMParts16.00Oct 30, 2010 9:08:00 AMOct 31, 2010 10:50:00 AM1278 (Ottime-DateChanged)

77,42248,242Oct 29, 2010 11:50:00 AMParts16.00Decision10/30/2010 1:00:00 PMOct 30, 2010...

need to find the average frequency of the comments for each eventid

I tried writing this query but its giving me some errors...Please help me with this...

select (select avg("difference") from t...

Hey got exactly what I am looking for...

SELECT a.eventid, CONVERT(VARCHAR(10),a.localstarttime,111) xyz, b.refeventid, b.comment, b."timestamp",

DATEDIFF(second, (SELECT MAX("timestamp") FROM asom_comments WHERE refeventid=b.refeventid AND "timestamp"<b."timestamp"), b."timestamp") "difference"

FROM X a JOIN Y b...

Hey got exactly what I am looking for through some other friend....:-D

SELECT a.eventid, CONVERT(VARCHAR(10),a.localstarttime,111) xyz, b.refeventid, b.comment, b."timestamp",

DATEDIFF(second, (SELECT MAX("timestamp") FROM asom_comments WHERE refeventid=b.refeventid AND "timestamp"<b."timestamp"), b."timestamp") "difference"

FROM X...

was that good enough or do you want me to post the contents in the tables X and Y as well.

Please let me know....

Thanks in advance.

I am trying to understand this but my actual prob is that I have two tables(X,Y) in which

X:

'eventID' 'Station' 'StartTime'

with 'z' no .of rows

Y:

'ID' 'refEventID' 'Comment' 'TimeStamp'

with 's' no .of...

I am sorry

I am new to SQL server(I am working on Cognos a reporting tool)

thank you for the advice and let me google that...

Thanks a lot Wayne...

Just now implemented the query but I am using SQL Server 2000 which is not allowing me to use row_number() as a function so I tried to...