SQL Server - Need Query for the Requirement

Asked By rajeev chandra on 15-Dec-11 11:02 PM
+--------------+---------------------+-----------------+
| employeeIdNo | employeeLogTime     | employeeLogType |
+--------------+---------------------+-----------------+
|            1 | 2011-08-16 14:59:08 | login           |
|            1 | 2011-08-16 15:00:06 | logout          |
|            1 | 2011-08-16 15:14:51 | login           |
|            2 | 2011-08-16 15:15:00 | login           |
|            1 | 2011-08-16 15:59:01 | logout          |
|            2 | 2011-08-16 15:59:09 | logout          |
+--------------+---------------------+-----------------+

I want to calculate each employee id totaltime based on employeeID

Please help me
Web Star replied to rajeev chandra on 15-Dec-11 11:11 PM
You need to get login time subtract from logout time based on group by employeeid
See this link give you details description step by step
http://searchoracle.techtarget.com/answer/Displaying-time-ranges-based-on-login-logout 
Anoop S replied to rajeev chandra on 15-Dec-11 11:14 PM
Try this, you could sum the times as seconds, then convert to hours, minutes and seconds:

select TotalSeconds / 3600 as [Hours], (TotalSeconds % 3600) / 60 as [Minutes], (TotalSeconds % 3600) % 60 as [Seconds]
from
(
select sum(datepart(hour, employeeLogTime) * 3600) + sum(datepart(minute, employeeLogTime) * 60) + sum(datepart(second, employeeLogTime)) as TotalSeconds groupby employeeIdNo
from tablename
) t
Jitendra Faye replied to rajeev chandra on 15-Dec-11 11:14 PM
Have some proper structure in table like column with in/out

Sample table
 
[id]   [User_id]           [Date_time]                 [in_out]
    1       1          2011-01-20 09:30:03                  1
    2       1          2011-01-20 11:30:43                  0
    3       1          2011-01-20 11:45:12                  1
    4       1          2011-01-20 12:59:56                  0
    5       1          2011-01-20 13:33:11                  1
    6       1          2011-01-20 15:38:16                  0
    7       1          2011-01-20 15:46:23                  1
    8       1          2011-01-20 17:42:45                  0
And the query
SELECT `User_id`, time(sum(`Date_time`*(1-2*`in_out`)))
  FROM `whatever_table` GROUP BY `User_id`;


The (1-2*`in_out`) term gives every login event a -1 factor and every logout event a +1 factor. The sum function takes the sum of the Date_time column, and GROUP BY `User_id` makes that the sum for each different user is created.
rajeev chandra replied to Jitendra Faye on 15-Dec-11 11:33 PM
Please i need the query in sql server
Jitendra Faye replied to rajeev chandra on 15-Dec-11 11:41 PM
Try like this-


 table structure:

EID EDATETIME             CARDNUMBER
1   2003-04-25 14:26:27.000   10024        -----In
1   2003-04-25 14:26:44.000   10054        -----Out    
2   2003-04-25 14:26:03.000   10024
2   2003-04-25 14:26:14.000   10054

Query-


SELECT CardNumber, SUM(MinWorked) as TotalWorked
FROM(SELECT TimeIn.CardNumber, TimeIn.DateTime PunchIn,
            TimeOut.DateTime PunchOut, 
            DateDiff(minute, TimeIn.DateTime, TimeOut.Datetime) as MinWorked
     FROM CardTransactions TimeIn, CardTransactions TimeOut
     WHERE TimeIn.CardNumber = TimeOut.CardNumber
     AND DateDiff(day, TimeIn.DateTime, TimeOut.DateTime) = 0
     AND TimeIn.EID = 1
     AND TimeIn.DateTime > '1/1/1900'
     AND TimeIn.DateTime < '1/1/9999'
    AND TimeOut.EID = 2) Tmp
Group By CardNumber
Order by CardNumber


Hope this will help you