SQLTeam.com | Weblogs | Forums

Row count discounting subsequent records

sql2012
#1

I have data in which I would like to count the rounds of a game, I got close but when there are subsequent records by the same player then I don't want to count it, for example:

DECLARE @Scores table 
( 
	Player varchar(10), 
	Score varchar(20), 
	DateEntered datetime 
)

INSERT INTO @Scores (Player, Score, DateEntered) VALUES
('Bob', 5, '2019-05-10 14:00:00'),
('Tim', 2, '2019-05-10 14:00:13'),
('Jim', 1, '2019-05-10 14:00:24'),
('Jim', 2, '2019-05-10 14:00:29'),
('Bob', 8, '2019-05-10 14:00:45'),
('Tim', 3, '2019-05-10 14:00:49'),
('Jim', 5, '2019-05-10 14:00:55')

select row_number() over (partition by Player order by DateEntered) round, Player, Score
from @Scores order by DateEntered

This produces the following:

|round|Player|Score|
|1|Bob|5|
|1|Tim|2|
|1|Jim|1|
|2|Jim|2|
|2|Bob|8|
|2|Tim|3|
|3|Jim|5|

I want row 4 to be |1|Jim|2| and row 7 to be |2|Jim|5|

Any ideas on how I can accomplish this?

Thanks!

#2

I figured it out this way, if there is a simpler solution I am all ears :slight_smile:

Edit: so maybe not, it excludes the row rather than number it correctly.

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

select row_number() over (order by DateEntered) 'rn'
,Player, Score, DateEntered
into #temp from @Scores

select row_number() over (partition by player order by DateEntered) 'rn', * from #temp a
outer apply (
	SELECT Player as 'Prior'
	FROM #temp b
	where b.RN = a.RN-1
) b
where a.player <> b.[Prior] or b.[Prior] is null
order by a.DateEntered
#3

Something like this:

select sum(same) over(order by dateentered
                      rows unbounded preceding
                     )
       as [round]
      ,player
      ,score
  from (select *
              ,case when player=lag(player) over(order by dateentered) then 0 else 1 end as same
          from @scores
       ) as a
;
#4

You can use Update query like:
Update Scores
SET Score = '4', Player= 'Jim', Round='2'
WHERE DateEntered = '14:00:29':

#5

Slight modification and I got there, thanks!

select count(case when same = 1 then 1 else null end) over(partition by player order by dateentered
                  rows unbounded preceding
                 )
   as [round]
  ,player
  ,score
  from (select *
          ,case when player=lag(player) over(order by dateentered) then 0 else 1 end as same
      from @scores
   ) as a order by a.DateEntered
;

However there is one scenario this doesn't account for, when a player skips a turn:

DECLARE @Scores table
(
Player varchar(10),
Score varchar(20),
DateEntered datetime
)

INSERT INTO @Scores (Player, Score, DateEntered) VALUES
('Bob', 5, '2019-05-10 14:00:00'),
('Jim', 1, '2019-05-10 14:00:24'),
('Jim', 2, '2019-05-10 14:00:29'),
('Bob', 8, '2019-05-10 14:00:45'),
('Tim', 3, '2019-05-10 14:00:49'),
('Jim', 5, '2019-05-10 14:00:55')

In this instance Tim comes up with round 1, even though its in the middle of round 2. Another slight modification and I came up with this:

select Player, Score, case when [round]<lag([round]) over(order by dateentered) then lag([round]) over(order by dateentered) else [round] end as 'Round'
from (
	select *,count(case when same = 1 then 1 else null end) over(partition by player order by dateentered rows unbounded preceding) as [round]
	from (
		select *, case when player=lag(player) over(order by dateentered) then 0 else 1 end as same	from @scores
	) as a
) b order by b.DateEntered

Thank you for your help!