0

This question is in continuation to Merging every two rows of data in a column in SQL Server

My eventtable structure..

Id    UserId     EventId        EventDateTime
1       1           A           18-06-2013 10:36
2       1           B           18-06-2013 10:40
3       1           C           18-06-2013 10:46
4       1           D           18-06-2013 10:50
5       1           A           18-06-2013 13:36

From the earlier question I got data in the following format..

UserId  EventStart  EventEnd
1        A           B
1        B           C

Now I would like to get the count of Unique 'EventStart' and 'EventEnd' and filter them by UserId and Date/s

The Report format is 

EventStart EventEnd Count
 A           B       5
 B           C       3

I know that i could use the data from the previous Question query and store it in a table and try as suggested here

But it would be great if I could get the data straight from the 'eventtable' to the report format with the 'UserId' and 'Date' filters

Any help is sincerely appreciated..

Thanks..

Community
  • 1
  • 1
Arnab
  • 2,324
  • 6
  • 36
  • 60

1 Answers1

0

If you just want to count the occurrences, use group by in essentially the same query:

select eventstart, eventend, count(*)
from (select userId, eventid as eventstart,
             (select top 1 t2.eventid
              from mytable t2
              where t2.userid = t.userid and
                    t2.id > t.id
              order by t2.id
             ) as eventend
      from mytable t
     ) t
where eventend is not null
group by eventstart, eventend;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, I was able to filter based on userId by adding a select and groupby UserId. How do I filter based on time period i.e if a user selects a date or time period, only those eventstart, eventend and count must be shown that happened during that period. Also, I wanted to add two more where clauses to the query with Or syntax - eventstart not in ('A', 'B') & eventstart !=eventend but I'm unable to get the OR clause working though AND clause is working- Thanks again for your help – Arnab Aug 05 '14 at 18:53
  • 1
    @Arnab . . . You would just use a `where` clause to do the filtering, regardless of the column. – Gordon Linoff Aug 05 '14 at 19:01