Check only the last record of multiple users

Navigation
0

Good day.

In a Log table, multiple users check in and check out several times during the day.

I want to determine the "Total" time in which the users were "inside" the system (the time comprised from the first check in and the last check out of the day).

I have divided the task in two because I have to see also who arrived at their time and who did not

The first part is already done, however the second part requires that you look for the last check out of all the users who showed up that day.

This is my code for the first part: 

SELECT * FROM logs WHERE 'Date' = '" & Date & "' AND 'Status' = 'Clocked In' group by 'UserName'

But I can not find the sentence that gives me only the last "Check out" record of all the users.

A capture of the table with the sentence: 

SELECT * FROM 'logs' WHERE 'Date' = '1/17/2018' And 'Status' = 'Clocked Out' order by 'username'

The idea is that the query only yields one result per user and that this result in its column Time is the largest value.

Thanks for your time.

    
asked by Neoz Memphisto 17.01.2018 в 20:43
source

1 answer

2

the only thing you should do is a 

select username,max(time) from logs 
 WHERE 'Date' = '1/17/2018' And 'Status' = 'Clocked Out' 
group by username
order by username
    
answered by 17.01.2018 / 21:34
source
Hover effect in C # panel ImportError at post / new when creating a form with Django