Query to Move Into Separate Column

Question

Currently i'm stuck for searching best query to make a resume of attendance log database.

Here is the current layout of 'absen' table:

id|employee_id|          log        |status
1 | 1         | 2019-03-27 07:58:32 |  in
2 | 1         | 2019-03-27 07:58:38 |  in
3 | 1         | 2019-03-27 17:12:34 |  out
4 | 2         | 2019-03-26 07:45:12 |  in
5 | 2         | 2019-03-26 18:22:54 |  out
6 | 1         | 2019-03-28 07:45:38 |  in
7 | 1         | 2019-03-28 17:10:55 |  out
8 | 2         | 2019-03-27 07:59:02 |  in
9 | 2         | 2019-03-27 18:35:16 |  out

And here is the goal

employee_id|          in         |        out
    2      | 2019-03-26 07:45:12 | 2019-03-26 18:22:54
    1      | 2019-03-27 07:58:32 | 2019-03-27 17:12:34
    2      | 2019-03-27 07:59:02 | 2019-03-27 18:35:16
    1      | 2019-03-28 07:45:38 | 2019-03-28 17:10:55

(ORDER BY in ASC)

I already try this query:

SELECT
    dateng.e_idIN AS "PIN",
    MASUK,
    PULANG
FROM
(
    SELECT
        MIN(log) AS "MASUK",
        employee_id AS e_idIN
    FROM
        absen
    WHERE
        status = "in"
    GROUP BY
        log
) dateng
INNER JOIN(
    SELECT
        MAX(log) AS "PULANG",
        employee_id AS e_idOUT
    FROM
        absen
    WHERE
        status = "out"
    GROUP BY
        log
) pergi
ON
    dateng.e_idIN = pergi.e_idOUT

but the result is not as expected

PIN |         MASUK       |         PULANG
2   | 2019-03-26 07:45:12 | 2019-03-27 18:35:16
2   | 2019-03-26 07:45:12 | 2019-03-26 18:22:54
1   | 2019-03-27 07:58:32 | 2019-03-28 17:10:55
1   | 2019-03-27 07:58:32 | 2019-03-27 17:12:34
1   | 2019-03-27 07:58:38 | 2019-03-28 17:10:55
1   | 2019-03-27 07:58:38 | 2019-03-27 17:12:34
2   | 2019-03-27 07:59:02 | 2019-03-27 18:35:16
2   | 2019-03-27 07:59:02 | 2019-03-26 18:22:54
1   | 2019-03-28 07:45:38 | 2019-03-28 17:10:55
1   | 2019-03-28 07:45:38 | 2019-03-27 17:12:34

Any clue for this task?

See https://stackoverflow.com/questions/7674786/mysql-pivot-table — RiggsFolly, Mar 27 '19 at 15:15
And https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql — RiggsFolly, Mar 27 '19 at 15:16
___but the result is not as expected___ Not a hepful description of your issue! Please show error messages or at least the result (if you get one) — RiggsFolly, Mar 27 '19 at 15:18
Hi @RiggsFolly, i have updated my question by adding the "unexpected" result — BlaxWalker, Mar 27 '19 at 15:30

forpas · Accepted Answer · 2019-03-27T16:06:16.533

0

I think that this will work:

select t.employee_id, min(t.login) login, t.logout  
from (
  select a.employee_id, a.log login, 
  (select min(log) from absen 
   where status = 'out' and employee_id = a.employee_id and log > a.log) logout
  from absen a
  where a.status = 'in'
) t
group by t.employee_id, t.logout  
order by t.login, t.logout

edited Mar 27 '19 at 16:06

answered Mar 27 '19 at 15:40

forpas

160,666
10
38
76

Hi @forpas, I tried your query and have an error on the first line. Changed min(t.log) into min(t.in) and the query can be run. However, there are flaws on the query result, which show in and out at wrong time for a row – BlaxWalker Mar 27 '19 at 16:03
I edited, changed the names from in and out which are reserved words to login logout. Try again and check if this was the problem. – forpas Mar 27 '19 at 16:07
AWESOME! It works now! It will take some time for me to digest your query, meanwhile I will kudos this as the answer... – BlaxWalker Mar 27 '19 at 16:11

Query to Move Into Separate Column

1 Answers1