SQL Query Daily Attendance Record

Question

Can you help me transform this data using query?

id	datetime	status
1	2022-04-01 07:38:31	Time In
1	2022-04-01 12:07:17	Break In
1	2022-04-01 12:07:39	Break Out
1	2022-04-01 16:43:17	Time Out
1	2022-04-02 07:38:31	Time In
1	2022-04-02 12:07:39	Break Out

id	date	Time In	Break In	Break Out	Time Out
1	2022-04-01	07:38:31	12:07:17	12:07:39	16:43:17
1	2022-04-02	07:38:31		12:07:39
1	2022-04-03
1	2022-04-04
1	2022-04-05
1	2022-04-06

The table date should be up to 2022-04-30.

Answer 1

You'll need to make the date function correct for the type of SQL you're using, this is T-SQL (MS SQL Server) code:

SELECT ID, 
       convert(date,Datetime,111) as Day,
       MAX(CASE WHEN status = 'Time In' THEN Datetime ELSE NULL END) as TimeIn,
       MAX(CASE WHEN status = 'Break In' THEN Datetime ELSE NULL END) as BreakIn,
       MAX(CASE WHEN status = 'Break Out' THEN Datetime ELSE NULL END) as BreakOut,
       MAX(CASE WHEN status = 'Time Out' THEN Datetime ELSE NULL END) as TimeOut
 FROM mytest
GROUP BY ID, 
         convert(date,Datetime,111)

A suggestion: consider changing the name of the datetime column - giving a column a reserved word as a column name will come back to haunt you at some point.

Also, if you want just the time portion, then apply whatever logic is appropriate to your SQL flavor to parse that from the Datetime.