0

Good afternoon, i want to ask about MySql query.

I have the initial data as pict below.

initial datas

I want to calculate the number of hours difference by displaying several data columns with the MySql query below.

SELECT id AS id,beacon_name AS Name,MIN(received_date) as tap_in,MAX(received_date) as tap_out,TIMESTAMPDIFF(HOUR,MIN(received_date),MAX(received_date)) AS work_hours from tb_gate_log where date(received_date) ='2023-06-06' group by id;

and it works like pict below.

result

however, there are cases such as there is only one data with status only "OUT" or "IN" for one id. I want the result like pict below when have status "Out" or "IN" only in one id. enter image description here

how i do the query with result i wanted?

thank you,

regards.

Improve this question
1
  • It sounds like you need a table with all possible values, then LEFT JOIN from that table to your table. You may also need a COALESCE to turn NULL into 0. Commented Jun 10, 2023 at 6:22

1 Answer 1

Reset to default
0 
SELECT id AS id,
  beacon_name AS Name,
  MIN(case when status = 'IN' then received_date end) as tap_in,
  MAX(case when status = 'OUT' then received_date end) as tap_out,
  COALESCE(TIMESTAMPDIFF(HOUR,
    MIN(case when status = 'IN' then received_date end),
    MAX(case when status = 'OUT' then received_date end)), 0) AS work_hours 
from tb_gate_log 
where date(received_date) ='2023-06-06' 
group by id
;
Improve this answer
1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. Commented Jun 10, 2023 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.