CODEWITHSUNDEEP
sqlsql-server
WhoaItsAFactorial
WhoaItsAFactorial

Reputation: 3558

SQL Server - Get highest values when previous date not blank

I have a table that contains information for player's steps taken for any given day, I want to get the 3 highest totals from the database, when the day before the day of the high total has a value added to it for that player. Since some players are not logging everyday, but rather lumping multiple days into one update.

Data Structure:

|---------------------------------------------------------------|
| id | player_id | steps | date       | submitted               |
|----|-----------|-------|------------|-------------------------|
| 1  | 16        | 5718  | 2012-09-06 | 2012-09-10 08:31:35.000 |
| 2  | 16        | 9837  | 2012-09-07 | 2012-09-10 08:31:17.000 |
| 3  | 16        | 10432 | 2012-09-09 | 2012-09-10 08:30:54.000 |
| 4  | 30        | 3973  | 2012-09-07 | 2012-09-10 09:34:42.000 |
| 5  | 30        | 7104  | 2012-09-08 | 2012-09-10 09:35:05.000 |
| 6  | 30        | 10916 | 2012-09-09 | 2012-09-10 09:35:29.000 |
| 7  | 9         | 6437  | 2012-09-07 | 2012-09-10 09:39:15.000 |
| 8  | 9         | 9032  | 2012-09-08 | 2012-09-10 09:40:02.000 |
|---------------------------------------------------------------|

So what I want to return are the rows:

|---------------------------------------------------------------|
| 6  | 30        | 10916 | 2012-09-09 | 2012-09-10 09:35:29.000 |
| 2  | 16        | 9837  | 2012-09-07 | 2012-09-10 08:31:17.000 |
| 8  | 9         | 9032  | 2012-09-08 | 2012-09-10 09:40:02.000 |
|---------------------------------------------------------------|

My query is currently:

SELECT TOP 3 p.name,
             player_id,
             steps,
             date
FROM   steppers_step_log
       JOIN steppers_players P
         ON player_id = P.id
ORDER  BY steps DESC

Which does not take into account whether there is an entry for the date prior on the top 3 from steps, which with my query in its current form will return 10432, 10916, and 9837 as the top steps.

I am having trouble working out how to do the WHERE command to make sure that the previous day has an entry for that player_id and would appreciate any help in getting it working.

Upvotes: 2

Views: 335

Answers (3)

MatBailie
MatBailie

Reputation: 86765

You could simply add a WHERE clause using an EXISTS condition...

SELECT TOP 3
  p.name,
  player_id,
  steps,
  date
FROM
  steppers_step_log    AS log
INNER JOIN
  steppers_players     AS players
     ON log.player_id = players.id
WHERE
  EXISTS (
    SELECT *
      FROM steppers_step_log
     WHERE player_id = players.id
       AND date      = log.date - 1
  )
ORDER BY
  log.steps DESC

This can however, return the same player more than once; if they have more than one entry in the top 3. Is that what you want?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270443

There are several ways to solve this. Others will probably do the solution with a join/exists type query. I like an alternative approach that uses ranking functions to get the appropriate row.

What you are looking for are sequences of uploads, and then you don't want the first. You can define a sequence by enumerating the rows for a player. Then, for each sequence the difference between the date and the sequence is a constant. Then, additional rankings get what you want.

To define the sequences:

select stl.*,
       row_number() over (partition by player_id, groupid order by date) as seqnum
from (select stl.*,
             datediff(day, date,
                      row_number() over (partition by player_id order by date) as groupid
      from steppers_step_log stl
     ) stl

Now, you can get the "qualifying" rows by using:

where seqnum > 1

To get the three highest values for each player, you would can do:

with stl as (<above query>)
select *
from (select stl.*,
             row_number() over (partition by player_id order by steps desc) as scorenum
      from stl
      where seqnum > 1
     ) stl
where scorenum <= 3

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460208

Something like this?

SELECT TOP 3 p.name,
             player_id,
             steps,
             date
FROM   steppers_step_log
       JOIN steppers_players P
         ON player_id = P.id
WHERE EXISTS
(
   SELECT 1 FROM steppers_step_log ssp
   WHERE ssp.player_id = P.id
   AND date = DATEADD(day, -1, steppers_step_log.date)
)
ORDER  BY steps DESC

DATEADD (Transact-SQL)

Upvotes: 3

Related Questions