Query for attendance data table

Below is the query I tried to use to get first in and last out of employees for a period of time. I need to get details for each and every month but this gives me the first in of earlier month and the last out the entire table has. How can I specify a time period here ?

;WITH _FirstEntries AS (
SELECT DISTINCT FirstEntries.* FROM checkinout AS ID
OUTER APPLY
(SELECT TOP 1 *
FROM checkinout AS ID2
WHERE [checktype] = 'I'
AND ID2.userid = ID.userid
ORDER BY ID2.[checktime] ASC
, ID2.[userid] ASC) AS FirstEntries
)
, _LastExits AS(
SELECT DISTINCT LastExit.* FROM checkinout AS ID
OUTER APPLY
(SELECT TOP 1 *
FROM checkinout AS ID2
WHERE [checktype] = 'O'
AND ID2.userid = ID.userid
ORDER BY ID2.[checktime] DESC
, ID2.[userid] DESC) AS LastExit
)

SELECT * FROM
(
SELECT * FROM _FirstEntries

UNION ALL

SELECT * FROM _LastExits

) AS ResultUnSorted
ORDER BY userid, [checktype]

Below is the output

Logid Userid CheckTime CheckType


23 1 01/07/2016 20:13.0 I
4017 1 16/07/2016 10:19.0 O
143 109 04/07/2016 05:12.0 I
1762 109 14/07/2016 37:44.0 O
1876 11 15/07/2016 51:17.0 I
1510 11 13/07/2016 48:35.0 O
132 120 01/07/2016 48:50.0 I
4026 120 16/07/2016 12:17.0 O
82 125 01/07/2016 54:35.0 I

I'm not sure what it is exactly you want.

This query will show lowest/first in datetime and highest/last out datetime within specified period:

declare @startdt datetime=cast('2016-06-01' as datetime);
declare @enddt   datetime=cast('2016-07-01' as datetime);

select logid
     ,userid
     ,checktime
     ,checktype
  from (select logid
              ,userid
              ,checktime
              ,checktype
              ,row_number() over(partition by userid
                                 order by checktype
                                         ,checktime
                                )
               as rn1
              ,row_number() over(partition by userid
                                 order by checktype desc
                                         ,checktime desc
                                )
               as rn2
          from checkinout
         where checktime>=@startdt
           and checktime <@enddt
           and checktype in ('I','O')
       ) as a
 where rn1=1
    or rn2=1
;

This next query will show lowest/first in datetime and highest/last out datetime for each month within specified period:

declare @startdt datetime=cast('2016-06-01' as datetime);
declare @enddt   datetime=cast('2016-07-01' as datetime);

select logid
     ,userid
     ,checktime
     ,checktype
  from (select logid
              ,userid
              ,checktime
              ,checktype
              ,row_number() over(partition by userid
                                             ,month(checktime)
                                 order by checktype
                                         ,checktime
                                )
               as rn1
              ,row_number() over(partition by userid
                                             ,month(checktime)
                                 order by checktype desc
                                         ,checktime desc
                                )
               as rn2
          from checkinout
         where checktime>=@startdt
           and checktime <@enddt
           and checktype in ('I','O')
       ) as a
 where rn1=1
    or rn2=1
;

Logid Userid CheckTime CheckType Sensorid


23 1 01/07/2016 20:13.0 I
4017 1 16/07/2016 10:19.0 O
143 109 04/07/2016 05:12.0 I
1762 109 14/07/2016 37:44.0 O
1876 11 15/07/2016 51:17.0 I
1510 11 13/07/2016 48:35.0 O
132 120 01/07/2016 48:50.0 I
4026 120 16/07/2016 12:17.0 O
82 125 01/07/2016 54:35.0 I

Thank you bitsmed,

What I want is first "in" and last "out" for each and every employee for each and every day of the month. Is it possible ?

On your second post you have a new field (sensorid) which was not in your first post. Which one is the correct output?

This ought to do it:

declare @startdt datetime=cast('2016-06-01' as datetime);
declare @enddt   datetime=cast('2016-07-01' as datetime);

select logid
      ,userid
      ,checktime
      ,checktype
      ,sensorid
  from (select logid
              ,userid
              ,checktime
              ,checktype
              ,sensorid
              ,row_number() over(partition by userid
                                             ,cast(checktime as date)
                                 order by checktype
                                         ,checktime
                                )
               as rn1
              ,row_number() over(partition by userid
                                             ,cast(checktime as date)
                                 order by checktype desc
                                         ,checktime desc
                                )
               as rn2
          from checkinout
         where checktime>=@startdt
           and checktime <@enddt
           and checktype in ('I','O')
       ) as a
 where rn1=1
    or rn2=1
;

Thank you very much Bitsmed. It works perfectly...

How do I get total worked hours and Overtime hours ?

  • can a shift cross days? (worker punch in one day and punch out the day after)
  • what are your rules for overtime? (another table perhaps)

Hello bitsmed,

Shifts do not cross days.Working hours are 8.30 am to 5.30 pm. If a person stays at least one hour (or more) after 5.30 pm he is eligible for overtime (for total time he works after 5.30).

Employees does not have to cover shift to get their overtime, If they stay after 6.30 pm (check in time not considered if they work after 6.30 and they will get wages for their overtime) their overtime will calculate from 5.30 pm.

For an example if employee "A" check in at 11 am and he check out at 7.15 pm his overtime will be 1 hour and 45 minutes.

I think this might work for you:

declare @startdt datetime=cast('2016-06-01' as datetime);
declare @enddt   datetime=cast('2016-07-01' as datetime);

with cte
  as (select logid
            ,userid
            ,checktime
            ,checktype
            ,sensorid
            ,row_number() over(partition by userid
                                           ,cast(checktime as date)
                               order by checktype
                                       ,checktime
                              )
             as rn1
            ,row_number() over(partition by userid
                                           ,cast(checktime as date)
                               order by checktype desc
                                       ,checktime desc
                              )
             as rn2
        from checkinout
       where checktime>=@startdt
         and checktime <@enddt
         and checktype in ('I','O')
     )
select a.logid
      ,a.userid
      ,a.checktime as punchin
      ,b.checktime as punchout
      ,a.sensorid as punchinsensor
      ,b.sensorid as punchoutsensor
      ,datediff(minute,a.checktime,b.checktime) as workminute
      ,case
          when datepart(hour,b.checktime)*60
              +datepart(minute,b.checktime)
              >=(18*60+30) /* time 18:30 */
          then datepart(hour,b.checktime)*60
              +datepart(minute,b.checktime)
              -(17*60+30) /* time 17:30 */
          else 0
       end as overtimeminute
  from cte as a
       left outer join cte as b
                    on b.userid=a.userid
                   and cast(b.checktime as date)=cast(a.checktime as date)
                   and b.checktype='O'
                   and b.rn=1
 where a.checktype='I'
   and a.rn1=1
;

Thanks a lot bitsmed. It works fine. Thanks again for your support.