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:
x
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
Advertisement
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;