I am making a program for Time and Attendance.
I have a table with this fields
ATTEND_PK INTEGER NOT NULL,
EMP VARCHAR,
ATTEND_DATETIME TIMESTAMP,
USER_PK SMALLINT,
ATTEND_UPDATETIME TIMESTAMP);
here is the sample data
| ATTEND_PK | EMP | ATTEND_DATETIME |
| 1 | john | 1/23/2018 7:00:00 |
| 2 | john | 1/23/2018 12:00:00 |
| 3 | john | 1/23/2018 13:00:00 |
| 4 | john | 1/23/2018 16:00:00 |
| 5 | john | 1/24/2018 7:30:00 |
| 6 | john | 1/24/2018 11:50:00 |
| 7 | john | 1/24/2018 13:20:00 |
| 8 | john | 1/24/2018 16:40:00 |
and my desired output is this
| EMP | DATE | AM_IN | AM_OUT | AM_IN | AM_OUT |
| john | 1/1/2018 | NULL | NULL | NULL | NULL |
| john | 1/2/2018 | NULL | NULL | NULL | NULL |
| .... | ..... | NULL | NULL | NULL | NULL |
| john | 1/23/2018 | 7:00:00 | 12:00:00 | 1:00:00 | 4:00:00 |
| john | 1/23/2018 | 7:30:00 | 11:50:00 | 1:20:00 | 4:40:00 |
| .... | ..... | NULL | NULL | NULL | NULL |
| john | 1/31/2018 | NULL | NULL | NULL | NULL |
the time format of my desired output and my data is different and display the complete dates of the given month and year