CODEWITHSUNDEEP
phpmysqlgreatest-n-per-group
CompRx
CompRx

Reputation: 31

Mysql Grab last update by user id within a specified timeframe

I have two tables tc_users and tc_timeclock. I want to grab the current status of each active user. But only those that have made an update (clocked in or out) within the last 12 hours. I have gotten as far as figuring out how to grab all of the updates within the last 12 hours but not how to narrow it down to just the last one for each user.

Here are my tables:

tc_users:

user_id | first_name | last_name | active
-----------------------------------------
1 | Frank   | Zappa     | 1
2 | John    | Mcneely   | 1
3 | Bill    | Mckenna   | 1
4 | Mark    | langdon   | 1
5 | Steve   | Mcalister | 0
6 | William | Stevens   | 1
7 | John    | Jones     | 0

tc_timeclock:

tc_id | user_id | status | time_stamp
-------------------------------------
1  | 1 | IN  | 2012-11-28 09:00:25
2  | 2 | IN  | 2012-11-28 09:01:25
3  | 3 | IN  | 2012-11-26 09:03:25
4  | 4 | IN  | 2012-11-28 09:21:25
5  | 5 | IN  | 2012-11-28 09:01:12
6  | 6 | IN  | 2012-11-28 09:47:13
7  | 7 | IN  | 2012-11-12 09:00:22
8  | 7 | OUT | 2012-11-12 09:03:28
9  | 5 | OUT | 2012-11-28 09:21:25
10 | 6 | OUT | 2012-11-28 11:47:13
11 | 3 | OUT | 2012-11-26 11:03:25
12 | 2 | OUT | 2012-11-28 11:01:25
13 | 1 | OUT | 2012-11-28 11:27:25
14 | 4 | OUT | 2012-11-28 11:21:25
15 | 4 | IN  | 2012-11-28 12:21:25
16 | 1 | IN  | 2012-11-28 12:27:25
17 | 3 | IN  | 2012-11-26 12:03:25
18 | 6 | IN  | 2012-11-28 12:47:13
19 | 2 | IN  | 2012-11-28 12:01:25
20 | 1 | OUT | 2012-11-28 17:27:25
21 | 4 | OUT | 2012-11-28 17:21:25
22 | 3 | OUT | 2012-11-26 17:03:25
23 | 2 | OUT | 2012-11-28 17:01:25

So far This is the query I have come up with :

if now = 2012-11-28 18:00:00

SELECT first_name, last_name, status, time_stamp 
FROM tc_timeclock 
  INNER JOIN tc_users 
    ON tc_timeclock.user_id = tc_users.user_id 
WHERE tc_users.active = 1 
  AND tc_timeclock.time_stamp BETWEEN '2012-11-28 06:00:00' AND '2012-11-28 18:00:00'

This displays everything within the last 12 hours like so:

first_name | last_name | status | time_stamp
-----------------------------------------------------
Frank      | Zappa     | IN     | 2012-11-28 09:00:25
John       | Mcneely   | IN     | 2012-11-28 09:01:25
Mark       | langdon   | IN     | 2012-11-28 09:21:25
William    | Stevens   | IN     | 2012-11-28 09:47:13
William    | Stevens   | OUT    | 2012-11-28 11:47:13
John       | Mcneely   | OUT    | 2012-11-28 11:01:25
Frank      | Zappa     | OUT    | 2012-11-28 11:27:25
Mark       | langdon   | OUT    | 2012-11-28 11:21:25
Mark       | langdon   | IN     | 2012-11-28 12:21:25
Frank      | Zappa     | IN     | 2012-11-28 12:27:25
William    | Stevens   | IN     | 2012-11-28 12:47:13
John       | Mcneely   | IN     | 2012-11-28 12:01:25
Frank      | Zappa     | OUT    | 2012-11-28 17:27:25
Mark       | langdon   | OUT    | 2012-11-28 17:21:25
John       | Mcneely   | OUT    | 2012-11-28 17:01:25

The logic I am looking for: Grab the first and last name, current status and time the status was logged for each active user within the last 12 hours.

So output should be: 

first_name | last_name | status | time_stamp
Frank      | Zappa     | OUT    | 2012-11-28 17:27:25
John       | Mcneely   | OUT    | 2012-11-28 17:01:25
Mark       | langdon   | OUT    | 2012-11-28 17:21:25
William    | Stevens   | IN     | 2012-11-28 12:47:13

User 5 (Steve Mcalister) and 7 (John Jones) are not active so they are not displayed.

User 3 (Bill Mckenna)has no activity within the last 12 hours so he is also no diplayed.

Am I missing something really simple? (I am sure I am)

I have a work around if there is not a simple solution, I could always add two columns to the tc_users table: (tc_users.current_status, and tc_users.status_time) and simply make those the same as the last entry into the tc_timeclock table.

That way I could easily select them, I don't like the idea of making the data redundant though if I don't have to.

Upvotes: 1

Views: 131

Answers (2)

C4llumD
C4llumD

Reputation: 11

Something like this might work for you.

SQL:

SELECT `tc_users`.`first_name`, `tc_users`.`last_name`, `tc_timeclock`.`status`, `temp_table`.`last_updated`
  FROM `tc_users`
 INNER JOIN `tc_timeclock`
         ON `tc_users`.`user_id`=`tc_timeclock`.`user_id`
 INNER JOIN (
             SELECT `user_id`, MAX(`time_stamp`) AS `last_updated` FROM `tc_timeclock` GROUP BY `user_id`
            ) AS `temp_table`
         ON `tc_users`.`user_id`=`temp_table`.`user_id`
        AND `tc_timeclock`.`time_stamp`=`temp_table`.`last_updated`
 WHERE `tc_users`.`active`!=0
   AND `temp_table`.`last_updated`>(NOW() - INTERVAL 12 HOUR);

OUT:

+------------+-----------+--------+---------------------+
| first_name | last_name | status | last_updated        |
+------------+-----------+--------+---------------------+
| William    | Stevens   | IN     | 2012-11-28 12:47:13 |
| Frank      | Zappa     | OUT    | 2012-11-28 17:27:25 |
| Mark       | langdon   | OUT    | 2012-11-28 17:21:25 |
| John       | Mcneely   | OUT    | 2012-11-28 17:01:25 |
+------------+-----------+--------+---------------------+

HTH

Upvotes: 1

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT first_name, last_name, tml.status, tml.time_stamp 
FROM (SELECT * 
      FROM tc_timeclock tl
      WHERE tl.time_stamp = (SELECT MAX(time_stamp) 
                             FROM tc_timeclock
                             WHERE tl.user_id = user_id )) tml
  INNER JOIN tc_users 
    ON tml.user_id = tc_users.user_id 
WHERE tc_users.active = 1 
  AND tml.time_stamp BETWEEN '2012-11-28 06:00:00' AND '2012-11-28 18:00:00'

Result:

| FIRST_NAME | LAST_NAME | STATUS |                      TIME_STAMP |
---------------------------------------------------------------------
|      Frank |     Zappa |    OUT | November, 28 2012 17:27:25+0000 |
|       John |   Mcneely |    OUT | November, 28 2012 17:01:25+0000 |
|       Mark |   langdon |    OUT | November, 28 2012 17:21:25+0000 |
|    William |   Stevens |     IN | November, 28 2012 12:47:13+0000 |

Upvotes: 0

Related Questions