Adding time in SQL Server 2008

Question

I want to add time in SQL Server. I have a column called checktime in the database which is a datetime datatype. I want to add the time from the column checktime. How can I do this ??

I'm doing this calculation for calculating total hrs an employee has worked during a specified day.

My database look like this.. I want to add the time from checktime where checktype = 1 and checktype = 2 and then subtract the results. (checktype = 1 means check in and checktype = 2 means check out)

How can I do this??

  Id        EmpId              CheckTime                                CheckType
---------------------------------------------------------------------------------
  3           5             2013-01-03 09:00:15.000                         1 
  4           5             2013-01-03 11:00:00.000                         2 
  5           5             2013-01-03 11:30:00.000                         1 
  6           5             2013-01-03 13:00:00.000                         2 
  7           5             2013-01-03 13:30:00.000                         1 
  8           5             2013-01-03 16:00:00.000                         2 
  9           5             2013-01-03 16:30:00.000                         1 
 10           5             2013-01-03 18:00:00.000                         2

Can you elaborate on this? I am not understanding what you want to add to checktime. — Mr Zorn
– Mr Zorn, Commented Jan 4, 2013 at 4:54
i want to get the sum of checktime with checktype=1... (09:00:15.000 +11:30:00.000 +13:30:00.000+16:30:00.000 ) — thejus_r
– thejus_r, Commented Jan 4, 2013 at 5:09
If you are looking for total time an employee worked, you shoud be doing this (11:00:00.000-09:00:15.000)+(13:00:00.000-11:30:00.000).. so on and so forth. — Raj
– Raj, Commented Jan 4, 2013 at 6:04
i am looking for that... but an employee can checkin and checkout more than once... so i want to sum up all time intervals... — thejus_r
– thejus_r, Commented Jan 4, 2013 at 6:09

user985189 · Accepted Answer · 2013-01-04 06:19:19Z

1

This is what I would do, if I understand you question correctly:

SELECT
    EmpId,
    CheckTime,
    CheckType,
    ROW_NUMBER() OVER(PARTITION BY EmpId, DATEADD(dd,DATEDIFF(dd,0,cin.CheckTime),0), CheckType ORDER BY CheckTime) AS Seq
INTO
    #PreparedTable
FROM
    SourceTable


SELECT
    cin.EmpId,
    DATEADD(dd,DATEDIFF(dd,0,cin.CheckTime),0) AS CheckDate,
    (SUM(DATEDIFF(ss,cin.CheckTime,cout.CheckTime)) / 3600.0) AS HoursWorked
FROM
    #PreparedTable cin
JOIN
    #PreparedTable cout
    ON  (cin.EmpId = cout.EmpID)
    AND (DATEDIFF(dd,cin.CheckTime,cout.CheckTime) = 0)
    AND (cin.Seq = cout.Seq)
    AND (cin.CheckType = 1)
    AND (cout.CheckType = 2)

answered Jan 4, 2013 at 6:19

user985189

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding time in SQL Server 2008

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related