Get only list of user with latest update by days

I am struggling to write a query to display list of users that check-in in a day, but if the same user check-in twice or more, it would take the latest in a day. For example user A check-in in the morning, then check-in again in the evening, then the system will display only his latest check-in in the list of users that check-in in that day.

This is my table:

CHECKIN_TABLE

id | checkin_datetime        | user_id | office_id
1    2020-12-02 09:07:09.290   1         D01
2    2020-12-02 15:13:42.645   1         D02
3    2020-12-02 12:25:12.823   2         D01
4    2020-12-03 13:12:05.523   1         D01

Then I would like to write a query that generate output like this:

date       | user_id | office_id
2020-12-02   1         D02
2020-12-02   2         D01
2020-12-03   1         D01

Answer

You can use a correlated subquery:

select convert(date, checkin_datetime), user_id, office_id
from CHECKIN_TABLE ct
where ct.checkin_datetime = (select max(ct2.checkin_datetime)
                             from CHECKIN_TABLE ct2
                             where ct2.user_id = ct.user_id and
                                   convert(date, ct2.checkin_datetime) = convert(date, ct.checkin_datetime)
                           );

Or you can use row_number():

select convert(date, checkin_datetime), user_id, office_id
from (select ct.*,
             row_number() over (partition by ct.user_id, convert(date, ct.checkin_datetime)
                                order by ct.checkin_datetime desc
                               ) as seqnum
      from CHECKIN_TABLE ct
     ) ct
where seqnum = 1;