[Solved] How to find in and out time of every employee

by


You can group by id and cast(time_stamp as date) to create one row per person per day:

select  *
,       datediff(minute, first_in, last_out) as duration
from    (
        select  id
        ,       min(case when [Access Type] = 'IN' then time_stamp end) as first_in
        ,       max(case when [Access Type] = 'OUT' then time_stamp end) as last_out
        ,       cast(min(time_stamp) as date) as date
        from    Table1
        group by
                id
        ,       cast(time_stamp as date)
        ) as SubQueriesMustBeNamed

See it working at SQL Fiddle.

0

solved How to find in and out time of every employee