Hi, i have a table that can be recreated with the below:
create table stat
(
gameID int
,playID int
,name nVarchar(10)
,eventType nVarChar(2)
)
insert into stat (gameId, playId, name, eventType) values
(1 , 1, 'Jim', 'a'),
(1 , 2, 'Frank', 'g'),
(1 , 3, 'Bill', 'f'),
(1 , 4, 'Peter', 'g'),
(1 , 5, 'Bob', 'v'),
(1 , 6, 'Tony', 'w'),
(1 , 7, 'Michael', 'g'),
(1 , 8, 'Simon', 's'),
(1 , 9, 'Gary', 'v'),
(2 , 1, 'Steve', 'g'),
(2 , 2, 'Philip', 'v'),
(2 , 3, 'Mark', 'e'),
(2 , 4, 'Bob', 'g'),
(2 , 5, 'Mark', 'x'),
(2 , 6, 'Tony', 'g'),
(2 , 7, 'Michael', 'n'),
(2 , 8, 'Steve', 'u'),
(2 , 9, 'Grant', 'v')
What im trying to extract is......
Whenever a "V" event occurs, I want to return the first occurrence of "Name" in a row before the "V" event where the Name has a "G" event.
For example......game id 1, play id 5 has an event "V".........i want to return Peter in the result as Peter has the first "G" event before "V".
Frank at game id 1, play id 2 shouldn't be returned as Peter had the first "G" event before the "V" event
I would like to see the following returned, the Name and how many times it has occurred for that name
Name Count
Peter 1
Michael 1
Steve 1
Tony 1