Query to show data checkin , checkout, breakin breakout in 1 row

Question

I have structure data like this, I'm using SQL Server:

ID	User_ID	CheckinTime	Date
1	110	"2022-03-25 06:30:29"	"2022-03-25"
2	110	"2022-03-25 12:12:31"	"2022-03-25"
3	110	"2022-03-25 13:02:11"	"2022-03-25"
4	110	"2022-03-25 17:17:21"	"2022-03-25"
5	111	"2022-03-25 06:32:21"	"2022-03-25"
6	111	"2022-03-25 12:05:11"	"2022-03-25"
7	111	"2022-03-25 13:01:32"	"2022-03-25"
8	111	"2022-03-25 17:12:51"	"2022-03-25"
9	112	"2022-03-25 06:32:53"	"2022-03-25"
10	112	"2022-03-25 17:11:21	"2022-03-25"
11	113	"2022-03-25 22:10:53"	"2022-03-25"
12	113	"2022-03-26 07:11:21	"2022-03-26"

And I want to show data like this:

ID	User_ID	Date	Checkin	Breakout	Breakin	Checkout
1	110	"2022-03-25"	"2022-03-25 06:30:29"	"2022-03-25 12:12:31"	"2022-03-25 13:02:11"	"2022-03-25 17:17:21"
2	111	"2022-03-25"	"2022-03-25 06:32:21"	"2022-03-25 12:05:11"	"2022-03-25 13:01:32"	"2022-03-25 17:12:51"
3	112	"2022-03-25"	"2022-03-25 06:32:53"	NULL	NULL	"2022-03-25 17:11:21"
4	113	"2022-03-25"	"2022-03-25 22:10:53"	NULL	NULL	"2022-03-26 07:11:21"

Anyone please help how to make this query.

Im already try using this query :

show on 1 row, but only get max and min on that date

didnt get the result like i want, not show on 1 row

Answer 1

Using the window function row_number() over() and sum() over() in concert with a conditional aggregation

Example

Select ID = row_number() over (order by User_ID)
      ,User_ID
      ,Date = min(Date)
      ,CheckIn  = max(case when rn = 1          then CheckInTime end)
      ,BreakOut = max(case when rn = 2 and rc>2 then CheckInTime end)
      ,BreakIn  = max(case when rn = 3 and rc>3 then CheckInTime end)
      ,CheckOut = max(case when rn = 4 or rc=rn then CheckInTime end)
 From (
        Select * 
              ,rn = row_number() over (partition by User_ID Order by CheckinTime)
              ,rc = sum(1) over (partition by User_ID)
         from @YourTable
      ) A
 Group By User_ID

Results