SQLTeam.com | Weblogs | Forums

How to write a query sum of Logintime and logout time of employee


#1

EMPTIME--TABLE

EMPID LOGINTIME LOGOUTTIME
1 10:45AM 10:20pm
1 11.20AM 9:10pm
1 8:10 am 10:20M


#2

SO, for the first row, the login time is 10:45 and the logout is 22:20, the sum is 32 hours, 65 minutes or 33 hours, 5 minutes, Is that what you mean?


#3

No,i want output like total individual logintime hours and logouttimes.


#4

OK so the total login hours for empid 1 = 10:45 + 11:20 + 8:10 and total of the logout times = 22:20 + 21:20 + 22:20

Is that what you need?

Or are you looking for (22:20 - 10:45) + (21:10 - 11:20) + (22:20 - 8:10) ?


#5

excatly


#6

yes,i want like that..


#7

Which one? I put two options there.


#8

second one


#9

Try this:

DECLARE @e TABLE (empid int, logintime TIME, logouttime TIME)`
INSERT INTO @e(empid, logintime, logouttime) VALUES
(1, '10:45', '22:20'),
(1, '11:20', '21:10'),
(1, '8:10', '22:20')

SELECT empid, SUM(DATEDIFF(HOUR, logintime, logouttime)) AS hours
FROM @e
GROUP BY empid