As shown in the image, am having the following columns in the database, and i have to get in(F1) and out (F2) of each employee in the following format.
Expected Outcome :
IN = mode_T (F1)
OUT = mode_T(F2)
date, name,employee_id ,IN,OUT
(2018-01-01,e1,time1,time2)
(2018-01-01,e2,time1,time2)
(2018-01-01,e3,time1,time2)
Will you have only one F1 and one F2 per day? If so, then you can do it with a sub-query
SELECT date
, employee_id
, i.time AS TimeIn
, o.time AS TimeOut
FROM tablename i
JOIN (SELECT date
, employee_id
, time
FROM tablename
WHERE mode_t = 'F2') o ON i.employee_id = o.employee_id AND o.date = i.date
WHERE mode_t = 'F1'
If you can have multiple records per day…good luck. That takes a whole other level of sqlFU which takes a whole lot longer to get right.
FYI first two columns in your SELECT clause are ambiguous
also, you don’t actually need to use a subquery…
SELECT i.date
, i.employee_id
, i.time AS TimeIn
, o.time AS TimeOut
FROM tablename AS i
INNER
JOIN tablename AS o
ON o.employee_id = i.employee_id
AND o.date = i.date
AND o.mode_t = 'F2'
WHERE i.mode_t = 'F1'