Sql Server complex subquery

Question

Consider these two tables:

CREATE TABLE GAME_ROUNDS(
    RoundId int primary key NOT NULL,
    GameId int NOT NULL,
    RoundNumber int NOT NULL,
    Value varchar(20) ,
    Guess varchar(20) ,
    Answer varchar(20) ,
    Correct bit NOT NULL,       
    Seconds int NOT NULL,
    Milliseconds int NOT NULL)

CREATE TABLE GAMES(
    GameId int  primary key NOT NULL,
    GameTypeId int NOT NULL,
    GameDate datetime NOT NULL,     
    GameValues varchar(500) null)

Here is some sample data:

insert into games(gameid, gametypeid, gamedate, gamevalues) values(1, 1,'2011-11-01 08:00:16.790',NULL)
insert into games(gameid, gametypeid, gamedate, gamevalues) values(2, 1,'2011-11-02 10:48:37.257',NULL)
insert into games(gameid, gametypeid, gamedate, gamevalues) values(3, 1,'2011-11-03 15:35:52.160',NULL)
insert into games(gameid, gametypeid, gamedate, gamevalues) values(4, 1,'2011-11-03 14:35:52.160',NULL)
insert into games(gameid, gametypeid, gamedate, gamevalues) values(5, 2,'2011-10-30 21:28:27.803','00,01,02,03,04,05,06,07,08,09')
insert into games(gameid, gametypeid, gamedate, gamevalues) values(6, 2,'2011-11-02 21:28:14.770','00,01,02,03,04,05,06,07,08,09')
insert into games(gameid, gametypeid, gamedate, gamevalues) values(7, 2,'2011-11-03 21:28:14.770','00,01,02,03,04,05,06,07,08,09')
insert into games(gameid, gametypeid, gamedate, gamevalues) values(8, 2,'2011-11-04 21:32:15.470','10,11,12,13,14,15,16,17,18,19')
insert into games(gameid, gametypeid, gamedate, gamevalues) values(9, 2,'2011-11-05 22:32:15.470','10,11,12,13,14,15,16,17,18,19')
insert into games(gameid, gametypeid, gamedate, gamevalues) values(10, 2,'2011-11-06 21:32:15.470','10,11,12,13,14,15,16,17,18,19')

insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(1, 1, 1,'Feb', '4', '4', 1, 0, 500)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(2, 1, 2,'Jul', '0', '0', 1, 0, 500)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(3, 1, 3,'Mar', '4', '4', 1, 0, 531)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(4, 2, 1,'Dec', '6', '6', 1, 0, 437)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(5, 2, 2,'May', '2', '2', 1, 0, 484)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(6, 2, 3,'Sep', '6', '6', 1, 0, 500)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(7, 3, 1,'Mar', '4', '3', 0, 0, 515)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(8, 3, 2,'Apr', '0', '0', 1, 0, 484)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(9, 3, 3,'May', '2', '2', 1, 0, 500)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(10, 4, 1,'Oct', '1', '1', 1, 0, 468)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(11, 4, 2,'Jan', '1', '1', 1, 0, 500)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(12, 4, 3,'Nov', '4', '4', 1, 0, 484)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(13, 5, 1,'0', '0', '0', 1, 0, 637)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(14, 5, 2,'5', '6', '6', 1, 0, 875)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(15, 5, 3,'8', '3', '3', 1, 0, 531)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(16, 6, 1,'9', '4', '4', 1, 0, 472)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(17, 6, 2,'6', '0', '0', 1, 0, 563)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(18, 6, 3,'8', '3', '3', 1, 0, 443)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(19, 7, 1,'0', '0', '0', 1, 0, 468)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(20, 7, 2,'9', '4', '4', 1, 0, 456)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(21, 7, 3,'4', '5', '5', 1, 0, 547)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(22, 8, 1,'12', '1', '1', 1, 0, 615)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(23, 8, 2,'18', '1', '1', 1, 0, 701)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(24, 8, 3,'16', '6', '6', 1, 0, 515)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(25, 9, 1,'10', '5', '5', 1, 0, 500)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(26, 9, 2,'19', '2', '2', 1, 0, 546)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(27, 9, 3,'15', '4', '4', 1, 0, 515)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(28, 10, 1,'13', '2', '2', 1, 0, 484)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(29, 10, 2,'12', '1', '1', 1, 0, 484)
insert into game_rounds(roundid, gameid, roundnumber, value, guess, answer, correct, seconds, milliseconds) values(30, 10, 3,'11', '6', '6', 1, 0, 453)

These tables are part of a memory game that I'm working on. The details are not important but as you play the game your guess times will improve.

One example is a game where you have to have to give the month code when presented with a month. So Oct = 1, Nov=4, Dec=6 etc.

I want to write a query that tells me for each gametypeid and gamevalues combinition the gameid where an average guess of 500 milliseconds was achieved.

Here is what I have so far:

select vt.gametypeid, vt.gamevalues, min(vt.gamedate)
from (
         select dg.gametypeid, dg.gameid, dg.gamedate, dg.gamevalues, count(gr.gameid) as numberofrounds, cast(sum(gr.seconds*1000 + gr.milliseconds) as decimal)/cast(count(gr.gameid) as decimal) as avgguessmilliseconds
         from game_rounds gr (nolock)
         inner join games dg (nolock) on dg.gameid = gr.gameid
         where gr.gameid not in(select gameid from game_rounds (nolock) where correct = 0)
         group by gr.gameid, dg.gametypeid, dg.gamevalues, dg.gamedate, dg.gameid, dg.gamedate
         having cast(sum(gr.seconds*1000 + gr.milliseconds) as decimal)/cast(count(gr.gameid) as decimal) <= 500
) vt
group by vt.gametypeid, vt.gamevalues

Here is the result

gametypeid  gamevalues                                         gamedate
----------- -------------------------------------------------- -----------------------
1           NULL                                               2011-11-02 10:48:37.257
2           00,01,02,03,04,05,06,07,08,09                      2011-10-30 21:28:27.803
2           10,11,12,13,14,15,16,17,18,19                      2011-11-04 21:32:15.470

The problem is I don't have the GameId to reference back to. What I wan't is to get the gameid back in my result set.

Is there a way to do this using pure SQL (no cursor or table variable)?

Answer 1

I think this will do you. I've assumed that what you want is the FIRST game that fits the specified criteria (since you used a min of the gamedate in your example). Clearly there could be multiple games for each gametypeid/values combo that would fit the bill.

SELECT GameId, 
       GameTypeId, 
       GameValues, 
       GameDate 
FROM
(
   SELECT   G.GameId,
        G.GameTypeId,
        G.GameValues,
        G.GameDate,
        RANK() OVER(Partition By G.GameTypeId, G.GameValues Order By
               G.GameDate DESC) As Ranking 
    FROM    GAMES G
    JOIN    GAME_ROUNDS GR
        ON  G.GameId = GR.GameId
    GROUP
    BY      G.GameId,
        G.GameTypeId,
        G.GameValues,
        G.GameDate
    HAVING  SUM(CASE GR.Correct WHEN 0 THEN 1 ELSE 0 END) = 0
    AND     AVG(CAST(GR.Seconds * 1000 + GR.Milliseconds as DECIMAL)) <= 500
) CandidateResults
WHERE   Ranking = 1

Answer 2

My hesitant answer is "Yes". I think the APPLY operator is for you since you want results per row without using a cursor or table variable. Check this Link out.