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 