Hi,
Below query was written by "bitsmed" and it gives the total working hours and overtime as well. I have another table called userinfo in my database and it has two columns named userid and name.
-
Can I insert the name column to below query from userinfo table so I will get the names also in my output ?
-
If I need to get one persons overtime and worktime details how can I do it ?
[declare @startdt datetime=cast('2016-07-01' as datetime);
declare @enddt datetime=cast('2016-07-18' as datetime);
with cte
as (select checkinout.logid
,checkinout.userid
,checkinout.checktime
,checkinout.checktype
,checkinout.sensorid
,userinfo.Name
from checkinout inner join userinfo on userid,
,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')
inner join checkinout on userinfo.userid = checkinout.userid
)
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)
>=(1860+30) /* time 18:30 /
then datepart(hour,b.checktime)60
+datepart(minute,b.checktime)
-(1760+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.rn2=1
where a.checktype='I'
and a.rn1=1
;