June 18, 2012 at 3:02 pm
Yes,I am trying to find the first eventType of 3 AFTER the each( 6 , 1 ) pair in the table.
So,for example of occurance
Of eventype_id 6 , then 1 and then 3
June 18, 2012 at 3:03 pm
OK that is what I thought but wanted to make sure I understood.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2012 at 3:15 pm
I am getting start and endtime needed only username:
Select min(event_time)as End_time,A.Entry_Id as Entry_id,a.start_time
from dbo.activity B
Right join
(
select Acivity_id,entry_id,min(Event_Time) as start_time from dbo.activity doc
where doc.Eventy_type_id =6
and Event_Value like 'pending%'
group by Entry_Id,Acivity_id
)A on A.Entry_Id =B.Entry_Id and B.event_time > A.start_time
and B.Eventy_type_id =1
and B.Acivity_id> A.Acivity_id
group by A.Entry_Id,A.start_time
June 19, 2012 at 3:34 pm
This works on your sample data. I added the outer query because at least in your sample data the first row has a Event_Type_Id = 3. In the real data this may not be the case.
select Entry, start_time, End_time, Username from
(
Select row_Number() over (order by min(a.Activity_ID)) as RowNum, A.Entry_Id as Entry, a.start_time, min(b.event_time)as End_time, un.Username
from dbo.activity B
Right join
(
select Activity_ID, entry_id, min(Event_Time) as start_time from dbo.activity doc
where doc.Event_Type_Id = 6
and Event_Value like 'pending%'
group by Entry_Id,Activity_ID
)A on A.Entry_Id = B.Entry_Id and B.event_time > A.start_time
and B.Event_Type_Id =1
and B.Activity_ID > A.Activity_ID
left join
(
select Activity_ID, entry_id, Username
from dbo.activity u
where u.Event_Type_Id = 3
) un on un.Entry_ID = B.Entry_Id
and un.Activity_id = b.Activity_ID + 1
group by A.Entry_Id, A.start_time, un.username
) x
where x.RowNum > 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply