How do i group the time in and time out according to the employee number and date [closed]

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

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?

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.

Advertisement

Answer