attendence sql file[ ][2] I try to get attendance table data (which include the emp_no ,date ,time).i want insert the above table date as emp_no,date,time in and time out.time logic is show in below.
i try sql query
x
SELECT
EnrolledID,
Date,
CASE WHEN Time > '1899-12-30 03:00:00' && Time <= '1899-12-30 12:15:00' THEN Time
END AS time_in ,
CASE WHEN Time > '1899-12-30 12:15:00' && Time <= '1899-12-30 23:59:59' THEN Time
END AS time_out
from attendance
GROUP BY EnrolledID,Date
Output of above query
EnrolledID Date time_in time_out
23 2019-08-01 00:00:00 1899-12-30 07:54:40 NULL
23 2019-08-01 00:00:00 NULL 1899-12-30 16:01:40
23 2019-08-02 00:00:00 1899-12-30 07:42:35 NULL
23 2019-08-02 00:00:00 NULL 1899-12-30 16:02:53
23 2019-08-03 00:00:00 1899-12-30 07:37:41 NULL
Expected output.
EnrolledID Date time_in time_out
23 2019-08-01 00:00:00 1899-12-30 07:54:40 1899-12-30 16:01:40
23 2019-08-01 00:00:00 1899-12-30 07:42:35 1899-12-30 16:01:40
23 2019-08-02 00:00:00 1899-12-30 07:42:35 1899-12-31 16:15:33
23 2019-08-02 00:00:00 1899-12-30 07:11:51 1899-12-30 16:02:53
23 2019-08-03 00:00:00 1899-12-30 07:37:41 1899-12-31 16:15:00
how do i fixed this issue? how do i avoid NULL value coming?
Advertisement
Answer
Your sample results don’t make sense. You have duplicates for 23
/'2019-08-01'
, but I see no data supporting such a duplicate.
I suspect that what you really want is aggregation, something like this:
SELECT EnrolledID, Date,
MIN(Time) as time_in,
NULLIF(MAX(Time), MIN(Time)) as time_out
FROM attendance
GROUP BY EnrolledID, Date;
This assumes at most two rows per date per enrollee.