Adding another table to the sql query

[yasantha]

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.

1. Can I insert the name column to below query from userinfo table so I will get the names also in my output ?

2. 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
;

[bitsmed]

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

with cte
  as (select a.logid
            ,a.userid
            ,a.checktime
            ,a.checktype
            ,a.sensorid
            ,b.[name]
            ,row_number() over(partition by a.userid
                                           ,cast(a.checktime as date)
                               order by a.checktype
                                       ,a.checktime
                              )
             as rn1
            ,row_number() over(partition by a.userid
                                           ,cast(a.checktime as date)
                               order by a.checktype desc
                                       ,a.checktime desc
                              )
             as rn2
        from checkinout as a
             inner join userinfo as b
                     on b.userid=a.userid
                    and b.[name]='yasantha'
       where a.checktime>=@startdt
         and a.checktime <@enddt
         and a.checktype in ('I','O')
       from checkinout
 )
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.rn2=1
 where a.checktype='I'
   and a.rn1=1
;

[yasantha]

Hi bitsmed,

Thanks for the query, but it gave me below error.

Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'from'.

[yasantha]

Hello bitsmed,

Found some records not in the result of the query.

I need to take the first and last punch times to calculate the worktime and
the overtime. This two time records should not depend on "I" or "O" because
there is another checktype which logs as "2" (that is the breaktime
according to the fingerprint machine). If we can get users worktime and
overtime by the first and last punch times of users that would be great.

I need only the user id, first punch time as "in time" and the last punch
time as "out time", total work time and the overtime and the name related
to the user id as the output.

User name is in another table named userinfo. Userinfo has both userid
column and name column.

I think above result can be taken in one query.

My other requirement is to have a query which will give me attendance
report (only in time and out time) of specified user for a specified time
period.

Below are the columns of two tables.

USE [Att2003]
GO

INSERT INTO [dbo].[Userinfo]
([Userid]
,[Name]
,[Sex]
,[Pwd]
,[Deptid]
,[Nation]
,[Brithday]
,[EmployDate]
,[Telephone]
,[Duty]
,[NativePlace]
,[IDCard]
,[Address]
,[Mobile]
,[Educated]
,[Polity]
,[Specialty]
,[IsAtt]
,[Isovertime]
,[Isrest]
,[Remark]
,[MgFlag]
,[CardNum]
,[Picture]
,[UserFlag]
,[Groupid]
,[workdaylong]
,[Floorid])
VALUES
(<Userid, varchar(20),>
,<Name, varchar(50),>
,<Sex, varchar(10),>
,<Pwd, varchar(50),>
,<Deptid, int,>
,<Nation, varchar(50),>
,<Brithday, smalldatetime,>
,<EmployDate, smalldatetime,>
,<Telephone, varchar(50),>
,<Duty, varchar(50),>
,<NativePlace, varchar(50),>
,<IDCard, varchar(50),>
,<Address, varchar(150),>
,<Mobile, varchar(50),>
,<Educated, varchar(50),>
,<Polity, varchar(50),>
,<Specialty, varchar(50),>
,<IsAtt, bit,>
,<Isovertime, bit,>
,<Isrest, bit,>
,<Remark, varchar(250),>
,<MgFlag, smallint,>
,<CardNum, varchar(10),>
,<Picture, image,>
,<UserFlag, int,>
,<Groupid, int,>
,<workdaylong, int,>
,<Floorid, int,>)
GO

USE [Att2003]
GO

INSERT INTO [dbo].[Checkinout]
([Userid]
,[CheckTime]
,[CheckType]
,[Sensorid]
,[Checked]
,[WorkType]
,[AttFlag]
,[OpenDoorFlag])
VALUES
(<Userid, varchar(20),>
,<CheckTime, datetime,>
,<CheckType, varchar(2),>
,<Sensorid, varchar(10),>
,<Checked, bit,>
,<WorkType, int,>
,<AttFlag, int,>
,<OpenDoorFlag, bit,>)
GO

Please let me know if you need more information.

bitsmed, thanks again for your help.

Below is where all these started

Query for attendance data table

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 …

[bitsmed]

Try this:

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

with cte
  as (select a.logid
            ,a.userid
            ,a.checktime
            ,a.checktype
            ,a.sensorid
            ,b.[name]
            ,row_number() over(partition by a.userid
                                           ,cast(a.checktime as date)
                               order by a.checktype
                                       ,a.checktime
                              )
             as rn1
            ,row_number() over(partition by a.userid
                                           ,cast(a.checktime as date)
                               order by a.checktype desc
                                       ,a.checktime desc
                              )
             as rn2
        from checkinout as a
             inner join userinfo as b
                     on b.userid=a.userid
                    and b.[name]='yasantha'
       where a.checktime>=@startdt
         and a.checktime <@enddt
         and a.checktype in ('I','O')
 )
select a.logid
      ,a.userid
      ,a.[name]
      ,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.rn2=1
 where a.checktype='I'
   and a.rn1=1
;

Change this line to whatever username you are looking up:

                    and b.[name]='yasantha'

[yasantha]

Hello bitsmed,

Thank for your support. It would be good if I can lookup attendance details
by userid since it is unique. Name is just the users name.

And still the records with checktype "2" which represent the break time get
omit from the result. Only "I" and "O" get in to the result. Is it possible
take the first and last punch times or users whatever the checktype into
the query ? Then all the punches will participate I guess.

[yasantha]

Hello bitsmed,

Additional to last reply. Users some time accidentally select the wrong
checktype {In, Out or break time} and punch. So I need to be sure I capture
the total time they worked regardless the what state they select in the
fingerprint machine when they punch.

For an example if the user select only "IN" and did the punches the
calculation only select the time records as below.

Please give me a query which takes the first punch time (first check time)
and last punch time (last check time) for the worktime calculation.

1.   09:00 – IN – Check In
   

2.   12:00 – IN – Invalid
   

3.   13:00 – IN – Invalid
   

4.   18:00 – IN – Check Out

[bitsmed]

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

with cte
  as (select a.logid
            ,a.userid
            ,a.checktime
            ,a.sensorid
            ,b.[name]
            ,row_number() over(partition by a.userid
                                           ,cast(a.checktime as date)
                               order by a.checktype
                                       ,a.checktime
                              )
             as rn1
            ,row_number() over(partition by a.userid
                                           ,cast(a.checktime as date)
                               order by a.checktype desc
                                       ,a.checktime desc
                              )
             as rn2
        from checkinout as a
             inner join userinfo as b
                     on b.userid=a.userid
       where a.checktime>=@startdt
         and a.checktime <@enddt
         and a.userid=1 /* replace this value to lookup different user, or remove line if you want all users */
 )
select a.logid
      ,a.userid
      ,a.[name]
      ,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.rn2=1
 where a.rn1=1
;