SQLTeam.com | Weblogs | Forums

Looping through Rows and applying a count


#1

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


#2

Please explain the count.
Michael and Tony both occurs twice (once i both events) - should count be 1 ro 2?
Steve occurs twice in second event - should count be 2?


#3

Thanks for the reply but I have managed to get some help on this query.......for reference this did the trick ......

WITH BASE_DATA AS
(
SELECT
ST.gameId
,ST.playId
,ST.name
,ST.eventType
,SUM(CASE
WHEN ST.eventType = 'v' THEN 1
ELSE 0
END) OVER
(
ORDER BY ST.gameId
,ST.playId
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS ST_GRP
,ROW_NUMBER() OVER
(
ORDER BY ST.gameId
,ST.playId
) AS ST_RID
FROM dbo.stat ST
WHERE ST.eventType IN ('v','g')
)
,SORTED_SET AS
(
SELECT
BD.gameId
,BD.playId
,BD.name
,BD.eventType
,BD.ST_GRP
,BD.ST_RID
,ROW_NUMBER() OVER
(
PARTITION BY BD.ST_GRP
ORDER BY BD.ST_RID DESC
) AS BD_GRP_RID
FROM BASE_DATA BD
)
SELECT
SS.playID
,SS.[name]
,COUNT(SS.playID) AS [Count]
FROM SORTED_SET SS
WHERE SS.BD_GRP_RID = 1
AND SS.eventType = 'g'
GROUP BY SS.playID
,SS.[name];