2

I want to export data for specific month (based on user input) from MySQL table, I wan't to calculate difference between created_at for each day in that month.

attendance_id:

  • 1 = arrival
  • 2 = exit
  • 10 = work from home

When user gets to work his entrance is logged as 1 (arrival), when he leaves work his exit is logged as 2 (arrival). Users can also work from home, when they start to work its logged as 10 (work from home) when they stop its logged as 2 (exit). Table user_attendance

+-----+---------+---------------+----------------+----------------+
| id  | user_id | attendance_id |   created_at   |   updated_at   |
+-----+---------+---------------+----------------+----------------+
|  52 |      45 |             1 | 1.4.2015 6:48  | 1.4.2015 6:48  |
|  53 |      31 |             1 | 1.4.2015 6:52  | 1.4.2015 6:52  |
| 132 |      45 |             2 | 1.4.2015 13:58 | 1.4.2015 13:58 |
| 133 |      31 |             2 | 1.4.2015 14:32 | 1.4.2015 14:32 |
| 159 |      45 |             1 | 2.4.2015 6:49  | 2.4.2015 6:49  |
| 160 |      31 |             1 | 2.4.2015 6:52  | 2.4.2015 6:52  |
| 232 |      31 |             2 | 2.4.2015 15:06 | 2.4.2015 15:06 |
| 233 |      45 |             2 | 2.4.2015 15:09 | 2.4.2015 15:09 |
| 252 |      74 |            10 | 3.4.2015 6:52  | 3.4.2015 6:52  |
| 253 |      74 |             2 | 3.4.2015 15:52 | 3.4.2015 15:52 |
+-----+---------+---------------+----------------+----------------+

SQL:

CREATE TABLE IF NOT EXISTS `user_attendance` (
`id` int(11) NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `attendance_id` int(10) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2836 ;

My attempt:

SELECT A.user_id, A.created_at,HOUR(TIMEDIFF(B.created_at,A.created_at)) AS timedifference
FROM user_attendance A CROSS JOIN user_attendance B
WHERE B.id IN (SELECT MIN(C.id) FROM user_attendance C WHERE C.id > A.id) and A.user_id=45 and A.attendance_id in(1,2) and MONTH(A.created_at)=5
ORDER BY date(A.created_at) ASC

As you can see I have limited it to specific user and to two types (arrival and exit) but the results are not OK, because timedifference is 0.

SuperManSL
  • 1,306
  • 2
  • 12
  • 17

2 Answers2

0

In case the errors are excluded and the order of operations always are 1-2 and also it comes and goes out in same day my solution is:

SELECT A.user_id, A.created_at,HOUR(TIMEDIFF(B.created_at,A.created_at))
FROM user_attendance A,  user_attendance B
WHERE
    A.attendance_id in (1,10) AND
    B.attendance_id=2 AND
    A.user_id=45 AND
    B.user_id=A.user_id AND
    DAY(A.created_at)=DAY(B.created_at) AND
    MONTH(A.created_at)=5
ORDER BY date(A.created_at) ASC
Kancho Iliev
  • 701
  • 5
  • 13
  • Order of operation can also be 10-1. Except that, I really like your solution. – SuperManSL May 24 '15 at 10:57
  • Arrival is always closed on the same day (by CRON job) so order 1-2 is limited to only one day. But like I said, between can be order 1-8-9-2 (arrival,lunch,lunch stop,exit). – SuperManSL May 24 '15 at 11:08
  • About 10 - it is updated. The order 1-8-9-2 is not changing anything in my case. – Kancho Iliev May 24 '15 at 11:33
  • Is it possible to handle if there are more 1-10 actions in one day? (user logins and logouts twice in a day and that gives an error) – SuperManSL Jul 06 '15 at 18:18
0
      select  ua.day(a.created_at) as day,ua.user_id, HOUR(timediff(max(created_at),min(created_at))) as TimeDiff
from user_attendance ua
where ua.created_at between '2015-05-01 00:00:00' and '2015-05-31 23:59:59'
group by day(ua.created_at),ua.user_id
order by ua.user_id
Abhishek Ginani
  • 4,511
  • 4
  • 23
  • 35
  • What is the meaning to use group by ua.day(a.created_at) in case you've defined single date in where clause? – Kancho Iliev May 24 '15 at 10:00
  • Your answer is great, because it compares first and last created_at so it doesn't matter what kind of attendance_id entrance has. One thing that I must mention is that you have typo at ua.day(a.created_at), it should be DAY(ua.created_at) , this typo is at selected fields and in group by clause. – SuperManSL May 24 '15 at 11:05