-1

I am trying to attain the minimum timestamp organized per user then date for a particular action. Thank you for any help.

I researched the code below, but unsure.

SELECT a1.*
FROM accesscards a1
JOIN (SELECT name, MIN(entrydates) mindate
      FROM accesscards
      WHERE name != ''
      GROUP BY name, date(entrydates)) a2
ON a1.name = a2.name AND a1.entrydates = a2.mindate

from Get records for each person's each day's min datetime

Current Table

+----------+------+--------+---------------------+
| idRecord | user | action | recorded            |
+----------+------+--------+---------------------+
| 1        | bob  | start  | 2018-01-27 01:00:01 |
+----------+------+--------+---------------------+
| 2        | amy  | close  | 2018-01-27 01:00:01 |
+----------+------+--------+---------------------+
| 3        | bob  | start  | 2018-01-27 02:00:01 |
+----------+------+--------+---------------------+
| 4        | amy  | start  | 2018-01-27 00:00:01 |
+----------+------+--------+---------------------+
| 5        | amy  | start  | 2018-01-28 00:00:01 |
+----------+------+--------+---------------------+
| 6        | jim  | open   | 2018-01-28 00:00:01 |
+----------+------+--------+---------------------+
| 7        | bob  | close  | 2018-01-27 02:00:01 |
+----------+------+--------+---------------------+
| 8        | bob  | start  | 2018-01-28 00:00:01 |
+----------+------+--------+---------------------+
| 9        | jim  | close  | 2018-01-28 00:00:01 |
+----------+------+--------+---------------------+

Desired Result

+------+----------+--------+---------------------+
| User | Date     | action | recorded            |
+------+----------+--------+---------------------+
| amy  | 01-27-18 | start  | 2018-01-27 00:00:01 |
+------+----------+--------+---------------------+
| amy  | 01-28-18 | start  | 2018-01-28 00:00:01 |
+------+----------+--------+---------------------+
| bob  | 01-27-18 | start  | 2018-01-28 01:00:01 |
+------+----------+--------+---------------------+
| bob  | 01-28-18 | start  | 2018-01-28 00:00:01 |
+------+----------+--------+---------------------+
| jim  | 01-28-18 | start  | 2018-01-28 00:00:01 |
+------+----------+--------+---------------------+
newpie
  • 77
  • 8
  • 1
    The query you posted has nothing to do with your table. You didn't even try to adjust the column names. And your problem is not even that complex. You need a basic GROUP BY query. – Paul Spiegel Jan 27 '18 at 16:44

1 Answers1

1

You need to group by with name, action and date(entrydates)

SELECT name, date(entrydates), action, MIN(entrydates)
FROM accesscards
WHERE action='start'
group by name, action, date(entrydates)

using WHERE action='start' ensure we don't group multiple instance ('start' and 'close') also, action type start is always going to be minimum.

Ravi
  • 30,829
  • 42
  • 119
  • 173