Row count discounting subsequent records

Transact-SQL
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!

6

hi WebKill

i tried a different approach
if it helps great
:slight_smile: :slight_smile:
i love any feedback thanks

create data .. 
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 * from @Scores
SQL .. different approach 
 ;WITH cte 
     AS (SELECT Iif(player <> 'JIM', Row_number() 
                                       OVER( 
                                         partition BY player 
                                         ORDER BY dateentered), Row_number() 
                   OVER( 
                     partition BY player 
                     ORDER BY dateentered) - 1) 
                rn, 
                * 
         FROM   @Scores) 
SELECT Replace(rn, 0, 1), 
       b.player, 
       b.score 
FROM   cte a 
       JOIN @Scores b 
         ON a.dateentered = b.dateentered 
ORDER  BY a.dateentered
Resullt

image