October 22, 2010 at 9:30 am
Hello,
I have three columns in my table
eventid comment timestamp
1 fiu 2010-10-12 12:25:00
2 abc 2010-10-13 03:19:00
2 xyz 2010-10-13 03:20:00
2 123 2010-10-13 03:29:00
3 kri 2010-10-14 20:29:00
and I am trying to get the time difference between the rows with same eventid
and I have tried this with some friend's help
select k.* from
(select a.eventid,
substring(convert(varchar(8),a.localstarttime,112),1,4)+'/'
+substring(convert(varchar(8),a.localstarttime,112),5,2)+'/'
+substring(convert(varchar(8),a.localstarttime,112),7,2) xyz,
b.refeventid, b.comment, b."timestamp"
from a join b
on a.eventid = b.refeventid
group by a.eventid, a.localstarttime, b.refeventid, b.comment, b."timestamp") k;
with k as (select * from (select ( select count(*) from k as e2 where e2.timestamp <= e1.timestamp) as rn, e1.* from k as e1) h)
select i1.*, datediff(mm, i1."timestamp", i2."timestamp") from k i1
left join k i2
on i1.eventid = i2.eventid and i1.rn=i2.rn+1;
error: incorrect syntax near the keyword 'with'.
Can you please help me with that....
Thanks in advance...
October 22, 2010 at 9:39 am
Your friend solved the problem using a CTE, which is a feature not available in sql server 2000. The solution can be reworked to work in sql server 2000, but in order for us to help you with that I'll need you to post the schema of all tables involved, with some sample data, and a sample output.
October 22, 2010 at 9:43 am
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 rows
I need to find the time difference between each comment with the same eventID (refEventID) per station and StartTime
so what I have done is that
select a.eventid, a.stn,
substring(convert(varchar(8),a.localstarttime,112),1,4)+'/'+substring(convert(varchar(8),a.localstarttime,112),5,2)+'/'
+substring(convert(varchar(8),a.localstarttime,112),7,2) xyz,
b.refeventid, b.comment, b."timestamp"
from X a join Y b
on a.eventid = b.refeventid
group by a.eventid, a.stn, a.localstarttime, b.refeventid, b.comment, b."timestamp"
so after writing this query I got the result like
(eventid) (comment) (timestamp)
1 fiu 2010-10-12 12:25:00
2 abc 2010-10-13 03:19:00
2 xyz 2010-10-13 03:20:00
2 123 2010-10-13 03:29:00
3 kri 2010-10-14 20:29:00
with 'q' no. of rows and now I want to get the time difference between the timestamps with same eventID.
October 22, 2010 at 9:45 am
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.
October 22, 2010 at 11:34 am
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 ON a.eventid = b.refeventid
order by a.eventid, a.xyz
really liked this solution....
October 22, 2010 at 2:39 pm
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 where eventid=k.eventid and "timestamp"< k."timestamp") average from
(SELECT a.eventid, CONVERT(VARCHAR(10),a.localstarttime,111) xyz, b.refeventid, b.comment, b."timestamp",
DATEDIFF(second, (SELECT MAX("timestamp") FROM Y WHERE refeventid=b.refeventid AND "timestamp"<b."timestamp"), b."timestamp") "difference"
FROM X a JOIN Y b ON a.eventid = b.refeventid
group by a.eventid, a.localstarttime, b.refeventid, b.comment, b."timestamp" ) t
join t k
on t.eventid = k.eventid
but its giving me an error saying that
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 't'.
My table t is
12827 2010/07/28 12827 waitin 2010-07-28 18:50:00 NULL
12827 2010/07/28 12827 wait/> 2010-07-28 18:52:00 120
12827 2010/07/28 12827 check 2010-07-28 19:28:00 2160
15167 2010/09/08 15167 test. 2010-09-09 18:35:00 NULL
15923 2010/09/22 15923 testt 2010-09-23 05:21:00 NULL
16812 2010/10/12 16812 wor.. 2010-10-13 03:19:00 NULL
16812 2010/10/12 16812 fie... 2010-10-13 03:20:00 60
16812 2010/10/12 16812 test.. 2010-10-13 03:29:00 540
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply