Рассчитать разницу между строками для каждого дня в месяце на основе типа
Я хочу экспортировать данные за определенный месяц (на основе пользовательского ввода) из таблицы MySQL, я не хочу рассчитывать разницу между созданным_каталогом для каждого дня в этом месяце.
attendance_id:
- 1 = прибытие
- 2 = выход
- 10 = работа из дома
Когда пользователь получает работу, его вход регистрируется как 1 (прибытие), когда он покидает работу, его выход регистрируется как 2 (прибытие).
Пользователи также могут работать из дома, когда они начинают работать, он записывается как 10 (работа из дома), когда они останавливаются, он регистрируется как 2 (выход).
Таблица 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 ;
Моя попытка:
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
Как видите, я ограничил его конкретным пользователем и двумя типами (прибытие и выход), но результаты не в порядке, потому что разница во времени равна 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
Другие решения
В случае, если ошибки исключены и порядок операций всегда 1-2, а также он приходит и уходит в тот же день, мое решение:
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