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 ?
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.