CODEWITHSUNDEEP
mysqlsql
Douglas Gaskell
Douglas Gaskell

Reputation: 10030

How to identify entries that have not changed over time?

I am trying to identify names that have not had a total score change over a period of time, and what that period of time was.

Example Table: 

+----------+--------+-------+
|   Date   |  Name  | Score |
+----------+--------+-------+
| 1/1/2016 | Frank  |    55 |
| 1/1/2016 | John   |    80 |
| 1/2/2016 | Frank  |    60 |
| 1/2/2016 | John   |    85 |
| 1/3/2016 | Frank  |    60 |
| 1/3/2016 | John   |   100 |
| 1/4/2016 | Frank  |    60 |
| 1/4/2016 | John   |   120 |
| 1/5/2016 | Frank  |    60 |
| 1/5/2016 | John   |   120 |
+----------+--------+-------+

Expected Output:

+-------+------+
| Name  | Days |
+-------+------+
| Frank |    4 |
| John  |    2 |
+-------+------+

I have been trying to puzzle out how to do this with no success. I have no code to show since none of it has even been close to successful and would only serve to clutter up the question.

How can I go about doing this?

Upvotes: 1

Views: 50

Answers (1)

Arturo Volpe
Arturo Volpe

Reputation: 3627

You need to group the data with the score, and then calculate the first and last day that the user has that score, check this:

SELECT DATEDIFF(last_day, first_day) + 1 AS days, name, score,
    first_day, last_day
FROM (
    SELECT 
      max(date_score) as last_day, 
      min(date_score) as first_day, 
      score, 
      name 
    FROM members
    GROUP by score
  ) AS score

The Date diff function return the difference between two DATE's, we add one to represent that the score last one day.

Check here for a working example link

Upvotes: 2

Related Questions