I have a database for attendance, it works fine as long as the person does not work over 2 dates. I want to utilize IN and OUT system for records but I do not know how to do the final step, and what I saw on the forum does not work on MySQL or I am doing something wrong there. This is my database and queries are under. BTW Database is built using PHPmyadmin and MySQL Workbench.
CREATE TABLE `entries` (
`indexing` int(11) NOT NULL,
`emp_id` int(5) NOT NULL,
`Date` datetime DEFAULT current_timestamp() ) ;
INSERT INTO `entries` (`indexing`, `emp_id`, `Date`) VALUES
(61, 1, '2020-07-07 05:41:36'),
(62, 1, '2020-07-07 05:44:21'),
(63, 2, '2020-07-07 05:44:36'),
(64, 3, '2020-07-07 05:49:23'),
(65, 2, '2020-07-07 05:49:39'),
(66, 3, '2020-07-07 05:50:00'),
(67, 4, '2020-07-07 09:56:51'),
(68, 5, '2020-07-07 09:57:13'),
(69, 3, '2020-07-07 09:57:18'),
(70, 2, '2020-07-07 09:57:28'),
(71, 1, '2020-07-07 09:57:42'),
(72, 4, '2020-07-07 09:57:49'),
(73, 5, '2020-07-07 09:59:38'),
(74, 1, '2020-07-08 05:59:42'),
(75, 2, '2020-07-08 06:00:05'),
(76, 3, '2020-07-08 06:38:20'),
(77, 1, '2020-07-08 09:47:43'),
(78, 4, '2020-07-08 09:56:14'),
(79, 5, '2020-07-08 09:56:47'),
(80, 1, '2020-07-08 09:56:59'),
(81, 3, '2020-07-08 09:57:34'),
(82, 2, '2020-07-08 09:58:07'),
(83, 4, '2020-07-08 09:58:11'),
(84, 5, '2020-07-08 09:59:20'),
(85, 5, '2020-07-08 09:59:50'),
(86, 4, '2020-07-08 11:08:36'),
(87, 3, '2020-07-08 11:09:30');
CREATE TABLE `user` (
`emp_id` int(5) NOT NULL,
`Name` varchar(50) NOT NULL,
`company` set('First','second') NOT NULL DEFAULT 'First',
`department` set('Outbound','Inbound','UE','Returns','QC','Cleaner','Admin','IT
Technician','Supervisor','Manager') NOT NULL,
`driver` set('PPT','VNA','HLOP','CB','PPT VNA HLOP','PPT HLOP','PPT CB') DEFAULT NULL
) ;
INSERT INTO `user` (`emp_id`, `Name`, `company`, `department`, `driver`) VALUES
(1, 'Micinka', 'second', 'IT Technician', ''),
(2, 'Dusbica', 'First', 'IT Technician', ''),
(3, 'Klaudocka', 'First', 'Returns', ''),
(4, 'Patrycginis', 'First', 'Cleaner', ''),
(5, 'Stuistow', 'First', 'Cleaner', '');
--
ALTER TABLE `entries`
ADD PRIMARY KEY (`indexing`),
ADD KEY `emp_id` (`emp_id`);
--
-- Indexes for table `user`
--
ALTER TABLE `user`
ADD PRIMARY KEY (`emp_id`);
-- Constraints for table `entries`
--
ALTER TABLE `entries`
ADD CONSTRAINT `entries_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `user` (`emp_id`) ON DELETE CASCADE;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
This are the Queries, and last one is how I would want the table look like but with IN and OUT times correct, now are both same.
select entries.emp_id, entries.Date, dense_rank() over (partition by entries.emp_id order by entries.indexing) % 2 AS 'IN and OUT' from entries;
drop table report_inout;
create view report_inout as select entries.emp_id, entries.Date,
CASE WHEN DENSE_RANK() OVER (PARTITION BY entries.emp_id ORDER BY entries.Date) % 2 = 0
THEN 'OUT' ELSE 'IN' END AS `IN and OUT`
FROM entries
ORDER BY
entries.indexing;
select date_format(report_inout.Date,'%d/%M/%Y') as `Date`,user.Name, time_format(report_inout.Date,'%H:%i:%s') as `IN`, time_format(report_inout.Date,'%H:%i:%s') as `OUT`,
user.company as Company,user.department as Department from report_inout
join user on user.emp_id = report_inout.emp_id
group by user.Name, report_inout.`In and Out`;
This are the results from my queries i posted.
emp_id;"Date";"IN and OUT" 1;"2020-07-07 05:41:36";"IN" 1;"2020-07-07 05:44:21";"OUT" 2;"2020-07-07 05:44:36";"IN" 3;"2020-07-07 05:49:23";"IN" 2;"2020-07-07 05:49:39";"OUT" 3;"2020-07-07 05:50:00";"OUT" 4;"2020-07-07 09:56:51";"IN" 5;"2020-07-07 09:57:13";"IN" 3;"2020-07-07 09:57:18";"IN" 2;"2020-07-07 09:57:28";"IN" 1;"2020-07-07 09:57:42";"IN" 4;"2020-07-07 09:57:49";"OUT" 5;"2020-07-07 09:59:38";"OUT" 1;"2020-07-08 05:59:42";"OUT" 2;"2020-07-08 06:00:05";"OUT" 3;"2020-07-08 06:38:20";"OUT" 1;"2020-07-08 09:47:43";"IN" 4;"2020-07-08 09:56:14";"IN" 5;"2020-07-08 09:56:47";"IN" 1;"2020-07-08 09:56:59";"OUT" 3;"2020-07-08 09:57:34";"IN" 2;"2020-07-08 09:58:07";"IN" 4;"2020-07-08 09:58:11";"OUT" 5;"2020-07-08 09:59:20";"OUT" 5;"2020-07-08 09:59:50";"IN"
and last query is this one, but it has always same time in IN and OUT
Date;"Name";"IN";"OUT";"Company";"Department" 08/July/2020;"Dusbica";"09:58:07";"09:58:07";"First";"IT Technician" 08/July/2020;"Dusbica";"06:00:05";"06:00:05";"First";"IT Technician" 08/July/2020;"Klaudocka";"09:57:34";"09:57:34";"First";"Returns" 08/July/2020;"Klaudocka";"11:09:30";"11:09:30";"First";"Returns" 08/July/2020;"Micinka";"09:47:43";"09:47:43";"second";"IT Technician" 08/July/2020;"Micinka";"09:56:59";"09:56:59";"second";"IT Technician" 08/July/2020;"Patrycginis";"11:08:36";"11:08:36";"First";"Cleaner" 08/July/2020;"Patrycginis";"09:58:11";"09:58:11";"First";"Cleaner" 08/July/2020;"Stuistow";"09:59:50";"09:59:50";"First";"Cleaner" 08/July/2020;"Stuistow";"09:59:20";"09:59:20";"First";"Cleaner"
Advertisement
Answer
Assuming that:
- 1st record for each separate
emp_idis IN event - There is no lost events
WITH cte AS ( SELECT emp_id, `Date`,
ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY `Date`) - 1 rn
FROM entries )
SELECT t1.emp_id, user.name, t1.`Date` in_date, t2.`Date` out_date
FROM user
JOIN cte t1 ON user.emp_id = t1.emp_id
LEFT JOIN cte t2 ON t1.emp_id = t2.emp_id
AND t1.rn DIV 2 = t2.rn DIV 2
AND t2.rn MOD 2
WHERE NOT t1.rn MOD 2
ORDER BY emp_id, in_date;
Idea.
We enumerate all rows for each employee separately starting with zero. So first IN is 0, first OUT is 1, 2nd IN is 2 and so on.
You can see that matched IN and OUT events will give the same result after integer divide their numbers by 2. And the reminder for IN will be 0 whereas for OUT it will be 1.
This is enough for correct joining.
Second copy of CTE table is joining using LEFT join because the last IN row may have no according OUT row – this means that the employee is now present at the object. And final row will contain NULL in out_date column in this case.