June 7, 2013 at 1:04 pm
Hi everyone.
I've got to figure out whose games scores changed the least over time. I've created DDL to illustrate
create table gamescores
(gameNo varchar(20) not null,
player varchar (20) not null,
score int not null,
datePlayed datetime not null,
memo varchar(20) null,
)
insert into gamescores
values
('Game1', 'P1', 50, getdate()-2, NULL),
('Game1', 'P2', 60, getdate()-2, NULL),
('Game1', 'P3', 70, getdate()-2, NULL),
('Game2', 'P1', 50, getdate()-1, NULL),
('Game2', 'P2', 65, getdate()-1, NULL),
('Game2', 'P3', 76, getdate()-1, NULL),
('Game3', 'P1', 50, getdate(), NULL),
('Game3', 'P2', 70, getdate(), NULL),
('Game3', 'P3', 81, getdate(), NULL)
;
as you can see, player 1's scores don't change. I want to write a query to capture in my bigger table, the player with No change to game scores, over a period of any 3 days.
Can you show me how? Thanks.
--Quote me
June 7, 2013 at 2:37 pm
polkadot (6/7/2013)
Hi everyone.I've got to figure out whose games scores changed the least over time. I've created DDL to illustrate
create table gamescores
(gameNo varchar(20) not null,
player varchar (20) not null,
score int not null,
datePlayed datetime not null,
memo varchar(20) null,
)
insert into gamescores
values
('Game1', 'P1', 50, getdate()-2, NULL),
('Game1', 'P2', 60, getdate()-2, NULL),
('Game1', 'P3', 70, getdate()-2, NULL),
('Game2', 'P1', 50, getdate()-1, NULL),
('Game2', 'P2', 65, getdate()-1, NULL),
('Game2', 'P3', 76, getdate()-1, NULL),
('Game3', 'P1', 50, getdate(), NULL),
('Game3', 'P2', 70, getdate(), NULL),
('Game3', 'P3', 81, getdate(), NULL)
;
as you can see, player 1's scores don't change. I want to write a query to capture in my bigger table, the player with No change to game scores, over a period of any 3 days.
Can you show me how? Thanks.
There's probably an easier way but this works:
SELECT * FROM(
SELECT player, max(score) a,min(score) b FROM gamescores
where gameNo in ('Game1','Game2','Game3')
group by player
)tab
where tab.a = tab.b
You basically take highest and lowest scores from each player and then narrow it down in the outer query. If max = min then it hasn't changed. edit: added in the where statement on inner query to limit weeks to those three like you asked.
Here's another way:
SELECT player from gamescores
where gameNo in ('Game1','Game2','Game3')
group by player
having max(score) = min(score)
June 7, 2013 at 3:14 pm
josh.granville (6/7/2013)
polkadot (6/7/2013)
Hi everyone.I've got to figure out whose games scores changed the least over time. I've created DDL to illustrate
create table gamescores
(gameNo varchar(20) not null,
player varchar (20) not null,
score int not null,
datePlayed datetime not null,
memo varchar(20) null,
)
insert into gamescores
values
('Game1', 'P1', 50, getdate()-2, NULL),
('Game1', 'P2', 60, getdate()-2, NULL),
('Game1', 'P3', 70, getdate()-2, NULL),
('Game2', 'P1', 50, getdate()-1, NULL),
('Game2', 'P2', 65, getdate()-1, NULL),
('Game2', 'P3', 76, getdate()-1, NULL),
('Game3', 'P1', 50, getdate(), NULL),
('Game3', 'P2', 70, getdate(), NULL),
('Game3', 'P3', 81, getdate(), NULL)
;
as you can see, player 1's scores don't change. I want to write a query to capture in my bigger table, the player with No change to game scores, over a period of any 3 days.
Can you show me how? Thanks.
There's probably an easier way but this works:
SELECT * FROM(
SELECT player, max(score) a,min(score) b FROM gamescores
where gameNo in ('Game1','Game2','Game3')
group by player
)tab
where tab.a = tab.b
You basically take highest and lowest scores from each player and then narrow it down in the outer query. If max = min then it hasn't changed. edit: added in the where statement on inner query to limit weeks to those three like you asked.
Here's another way:
SELECT player from gamescores
where gameNo in ('Game1','Game2','Game3')
group by player
having max(score) = min(score)
http://sqlfiddle.com/#!6/887a3/2%5B/quote%5D
An easier way that works for the sample data, but it won't work with a larger number of games.
SELECT player
FROM gamescores
GROUP BY player
HAVING STDEV( score) = 0
Do you need something for more than 3 games?
June 7, 2013 at 3:31 pm
I have a solution that will work with your sample data. I was trying to come up with something that works when there are more than 3 records but this is a tricky one. I will have a go at this tomorrow morning; I think this is close:
DECLARE @startDate date=(SELECT MIN(datePlayed) FROM #gamescores),
@endDate date=(SELECT MAX(datePlayed) FROM #gamescores);
WITH
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.all_columns),
dates(dt) AS (SELECT DATEADD(D,n,@startDate) FROM tally WHERE n<=DATEDIFF(D,@startDate,@endDate)),
prep AS
(SELECTg.gameNo, RANK() OVER (PARTITION BY player ORDER BY score) AS x1,
g.player,
g.score,
RANK() OVER (PARTITION BY player ORDER BY datePlayed) AS x2,
d.dt AS datePlayed --removed memo for now
FROM dates d
LEFT JOIN #gamescores g ON dt=CAST(datePlayed AS date))
SELECT *
FROM prep
WHERE x1=1 AND x2>=3;
-- Itzik Ben-Gan 2001
June 7, 2013 at 5:59 pm
I have a solution for you that will get the result for a player with 3 consecutive games with the same score.
drop table #gamescores
create table #gamescores
(gameNo varchar(20) not null,
player varchar (20) not null,
score int not null,
datePlayed datetime not null,
memo varchar(20) null,
)
insert into #gamescores
values
('Game0', 'P1', 40, getdate()-3, NULL),
('Game0', 'P2', 60, getdate()-3, NULL),
('Game0', 'P3', 70, getdate()-3, NULL),
('Game1', 'P1', 50, getdate()-3, NULL),
('Game1', 'P2', 60, getdate()-2, NULL),
('Game1', 'P3', 70, getdate()-2, NULL),
('Game2', 'P1', 50, getdate()-1, NULL),
('Game2', 'P2', 60, getdate()-1, NULL),
('Game2', 'P3', 76, getdate()-1, NULL),
('Game3', 'P1', 50, getdate(), NULL),
('Game3', 'P2', 70, getdate(), NULL),
('Game3', 'P3', 81, getdate(), NULL);
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY player ORDER BY datePlayed) rn
FROM #gamescores)
SELECT a.player
FROM CTE a
JOIN CTE b ON a.rn + 1 = b.rn AND a.player = b.player AND a.score = b.score
JOIN CTE c ON b.rn + 1 = c.rn AND b.player = c.player AND b.score = c.score
The next solution will give you the player with at least 3 games with the same score but might not be consecutive.
This solution IS NOT OPTIMAL for performance, and you should be aware of the problem that triangular joins represent, even if it's not a complete triangular join. More info[/url]
SELECT a.player
FROM #gamescores a
JOIN #gamescores b ON a.score = b.score AND a.gameNo < b.gameNo
JOIN #gamescores c ON b.score = c.score AND b.gameNo < c.gameNo
June 7, 2013 at 10:05 pm
Josh and Luis C's first one were creative/nice but don't capture scenario where only one game is played (in which case min/max are same) and they don't list all the games played for those players whose scores don't change.
Alan's does. I used it earlier today to solve my problem, though I had to take this on faith:
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.all_columns
Luis I'll take a look at your second. Thanks a lot folks for getting back to me right away. I really used each query. Very great.
--Quote me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply