CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Evil rising
Evil rising

Reputation: 442

Finding time difference from different records

I have database table which records Biometric data, for when USER ENTER/Leave office by swapping finger over it.

fields:

USERID EmpName, InOutTime, InOutDate

now when user enters office then it records 

+------+-------+---------+---------+
|USERID|EmpName|InOutTime|InOutDate|
+------+-------+---------+---------+
|001   |John   |08:30    |12-12-13 | //When user enters office
+------+-------+---------+---------+
|001   |John   |04:30    |12-12-13 | //When user leaves office
+------+-------+---------+---------+

Now only time changes each time but i want to find time difference (total work hours), from both records and show it then.

Caution: accidently user record can get enter more than once.

Upvotes: 1

Views: 81

Answers (2)

M.Ali
M.Ali

Reputation: 69524

Your Data

DECLARE @TABLE TABLE
 (USERID VARCHAR(10),EmpName VARCHAR(10),InOutTime TIME,InOutDate DATE)
 INSERT INTO @TABLE VALUES
('001','John','08:30','12-12-13'),('001','John','04:30','12-12-13'),
('002','Sam','05:30','12-12-13'),('002','Sam','04:30','12-12-13')

Query

SELECT    t1.EmpName
        , t1.InOutTime AS InTime
        , t2.InOutTime AS [TimeOut]
        , t1.InOutDate AS [DateVisited]
        , DATEDIFF(HOUR,t1.InOutTime, t2.InOutTime) TotalHours
FROM  @TABLE t1 INNER JOIN @TABLE t2
ON    t1.USERID = t2.USERID 
AND   t1.InOutDate = t2.InOutDate
AND   t1.InOutTime < t2.InOutTime

Result Set

╔═════════╦══════════════════╦══════════════════╦═════════════╦════════════╗
║ EmpName ║      InTime      ║     TimeOut      ║ DateVisited ║ TotalHours ║
╠═════════╬══════════════════╬══════════════════╬═════════════╬════════════╣
║ John    ║ 04:30:00.0000000 ║ 08:30:00.0000000 ║ 2013-12-12  ║          4 ║
║ Sam     ║ 04:30:00.0000000 ║ 05:30:00.0000000 ║ 2013-12-12  ║          1 ║
╚═════════╩══════════════════╩══════════════════╩═════════════╩════════════╝

Upvotes: 0

anon
anon

Reputation:

Here is a marginally useful query to see how bad the data structure is:

;WITH x AS 
(
  SELECT UserID, EmpName, InOutTime, InOutDate,
    rn = ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY InOutDate, InOutTime)
  FROM dbo.tablename
)
SELECT *, DATEDIFF(MINUTE, x.InOutTime, y.InOutTime)
  FROM x LEFT OUTER JOIN x AS y
  ON x.UserID = y.UserID AND x.rn = y.rn -1;

This will yield all kinds of crazy results in any case where the fingerprint wasn't scanned on the way out. This is what you get when you let non-database people design tables I guess. :-)

You really need to re-visit the design to fix the problems I outlined in my comment above.

Upvotes: 3

Related Questions