CODEWITHSUNDEEP
mysqlsql
AHMED NISAR
AHMED NISAR

Reputation: 23

How to fetch attendance report from attendance table through mysql query?

I create an attendances table. I insert data success fully but when fetch the data from attendances table, data not show of my needs attendance table.

in_out column keeps reference for in and out time. Value "1" is for in time and value "2" for out time. This is my query.

SELECT t.person_id, 
       t.date, 
       Substring_index(t.in_out, '#', 1)                           am_in, 
       Substring_index(Substring_index(t.in_out, '#', 2), '#', -1) am_out 
FROM   (SELECT h.person_id, 
               h.date, 
               Group_concat(h.timedata ORDER BY h.in_out SEPARATOR '#') in_out 
        FROM   attendances h 
        GROUP  BY h.person_id, 
                  h.date) t

Query result

When person out time dose not enter the vale of in time show in time_out column I want to show null column instead of same repeating time.

I want this result.

Required Result

Upvotes: 1

Views: 379

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try using CASE WHEN Expression

select t.person_id,t.date,
substring_index(t.in_out,'#',1) am_in, 
case when substring_index(t.in_out,'#',1)=substring_index(substring_index(t.in_out,'#',2),'#',-1) then 'Can not scan' else substring_index(substring_index(t.in_out,'#',2),'#',-1) end as am_out
from 
(
select h.person_id,h.date,group_concat(h.timedata order by h.in_out separator '#') in_out from attendances h group by h.person_id,h.date 
) t

Upvotes: 1

Related Questions