Calculate time difference (only working hours)

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Good day

I have a sql server table EmpAttendence in which there are two fileds:

EmployeeID , Attendence_Date
1111 , 2011-11-11 07:00:00.000
1111 , 2011-11-11 08:00:00.000
1111 , 2011-11-11 09:00:00.000
1111 , 2011-11-11 11:00:00.000
1111 , 2011-11-11 12:00:00.000
1111 , 2011-11-11 16:00:00.000
2222 , 2011-11-11 08:00:00.000

whenever employee punch or insert his time card in the time clock device, a new record is added to that table so that in one day he might have many records.

I need to write sql statement that show me for each day for each employee how many hours that employee has worked

No the Employee
1111 has worked 7 hours
followed by other employees

i tried the following
but giving me wrong result

SQL

CREATE TABLE #datetab 
  ( 
     employeeid      INT, 
     attendence_date DATETIME 
  ) 

INSERT INTO #datetab 
select FKemapEmID, CONVERT(DATETIME, CONVERT(CHAR(8), [apDate], 112) 
  + ' ' + CONVERT(CHAR(8), [apTime], 108))
  FROM [txnAcessPunches].[dbo].[atransAddPunch];
  
  
  
SELECT employeeid AS EMPID, 
       strdate    AS [Day], 
       time_in, 
       time_out, 
       Datediff(HH,time_in,time_out) as 'TotalHours'
FROM   (SELECT employeeid, 
               CONVERT(VARCHAR(10), attendence_date, 101) AS strDate, 
               Min(attendence_date)                       AS Time_in, 
               Max(attendence_date)                       AS Time_out 
        FROM   #datetab 
        GROUP  BY employeeid 
                  ,CONVERT(VARCHAR(10), attendence_date, 101)) t 
ORDER  BY employeeid, 
          time_in 
          
          
          drop table #datetab

Posted 19-May-14 4:16am

surajemo

Updated 19-May-14 4:21am

v2

Add a Solution

Comments

dan!sh 19-May-14 10:38am

Is the table in example all you got? How do you know which is in time and which is out time? And how is calculation done, to me it looks like 1111 worked for 9 hours.

surajemo 19-May-14 12:30pm

7 to 8 1hour
9 to 11 2 hours
12 to 16 hours 4 hours
= 7 hours

Maciej Los 19-May-14 16:47pm

Sorry, but how can you be sure that above hours are working hours?

surajemo 20-May-14 1:06am

Maciej the first punch will be in punch followed by out punch this how i am assuming because its a time clock machine where user will punch and data will be inserted row wise so i am assuming the first punch will be in punch next will out punch and so on
that is how i am saving the punches is there any other way of doing it which will be good i cant set flags to indicate this in punch this is out punch

2 solutions

Solution 1

I am not sure but may be this is useful for you.. :)

SQL

select datediff(hh,(select min(Attendence_Date) from tableName where EmployeeID=@EmployeeID and Convert(varchar,Attendence_Date,106)=Convert(varchar,@Date,106)),(select max(Attendence_Date) from tableName where EmployeeID=@EmployeeID and Convert(varchar,Attendence_Date,106)=Convert(varchar,@Date,106))) as Difference from tableName where EmployeeID=@EmployeeID and Convert(varchar,Attendence_Date,106)=Convert(varchar,@Date,106)

where @Date and EmployeeID is parameters to pass

Posted 19-May-14 4:38am

Nirav Prabtani

Updated 19-May-14 4:43am

v3

Comments

surajemo 20-May-14 1:07am

Thanks you for replying i will try it out :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Accepted Answer · 2014-05-19T10:47:00

Solution 2

Hi,

Let's say that we have the following data in a table EmpAttendence:

XML

EmployeeID	Attendence_Date
1111		2011-11-11 07:00:00.000
1111		2011-11-11 08:00:00.000
1111		2011-11-11 09:00:00.000
1111		2011-11-11 11:00:00.000
1111		2011-11-11 12:00:00.000
1111		2011-11-11 16:00:00.000
2222		2011-11-11 08:00:00.000
1111		2011-11-12 07:00:00.000
1111		2011-11-12 08:00:00.000

Query:

SQL

;WITH emp
     AS (SELECT [EmployeeID],
                [Attendence_Date],
                CAST([Attendence_Date] AS DATE)               AS [Day],
                Row_Number()
                  OVER(
                    PARTITION BY [EmployeeID], CAST([Attendence_Date] AS DATE)
                    ORDER BY [EmployeeID], [Attendence_Date]) AS [RowNumber]
         FROM   [dbo].[EmpAttendence])
SELECT t1.[EmployeeID]                                                          AS [EMPID],
       t1.[Day],
       MIN(t1.[Attendence_Date])                                                AS [time_in],
       MAX(t2.[Attendence_Date])                                                AS [time_out],
       SUM(ISNULL(DATEDIFF(HH, t1.[Attendence_Date], t2.[Attendence_Date]), 0)) AS [TotalHours]
FROM   emp AS t1
       LEFT JOIN emp AS t2
              ON ( t1.[EmployeeID] = t2.[EmployeeID]
                   AND t1.[Day] = t2.[Day]
                   AND t1.[RowNumber] = ( t2.[RowNumber] - 1 )
                   AND t2.[RowNumber] % 2 = 0 )
GROUP  BY t1.[EmployeeID],
          t1.[Day]
ORDER  BY t1.[EmployeeID],
          t1.[Day];

Here I am making an assumption that an employee should check out the same day as he checked in.

Result:

XML

EMPID Day 	  time_in		   time_out		    TotalHours
1111  2011-11-11  2011-11-11 07:00:00.000  2011-11-11 16:00:00.000  7
1111  2011-11-12  2011-11-12 07:00:00.000  2011-11-12 08:00:00.000  1
2222  2011-11-11  2011-11-11 08:00:00.000  NULL			    0

Posted 19-May-14 10:47am

Andrius Leonavicius

Updated 19-May-14 10:57am

v2

Comments

surajemo 20-May-14 3:42am

Thank you for replying it works can u explain me this part 
AND t1.[RowNumber] = ( t2.[RowNumber] - 1 ) 
AND t2.[RowNumber] % 2 = 0 ) 
 
and it only gives me hours 
if i want total minutes 
i need to write like this right 
SUM(ISNULL(DATEDIFF(MM, t1.[Attendence_Date], t2.[Attendence_Date]), 0)) AS [TotalMinutes],

Andrius Leonavicius 20-May-14 6:45am

You're welcome. Please accept (and rate) my solution. Thank you.

Explanation: Check in and check out times should be in the same row. In order to do this, I am using [RowNumber]: an odd number - check in time, an even number - check out time. For instance, 1 - in, 2 - out, 3 - in, 4 - out and so on. The first condition is used to join the separate time in and time out rows into single row using [RowNumber] like this: 1 = 2, 2 = 3, 3 = 4 and so on. The second condition is used to eliminate extraneous check out times from such join. By the way, you can add the following line above GROUP BY clause to remove unnecessary rows:
WHERE t1.[RowNumber] % 2 <> 0

If you want total minutes, you have to write MI instead of HH, not MM (MM stands for the month). Link: http://www.w3schools.com/sql/func_datediff.asp

surajemo 20-May-14 3:50am

I have Re-Written the query like this for retrieving only those employees data who have worked the number of specified hours to be marked as present i am also calculating the total minutes

declare @FromYear as int =2014
declare @ToYear as int =2015

declare @FromDate as date
declare @Todate as date
declare @GlbLeaveYear as int
SET @GlbLeaveYear = (SELECT glleavemonth FROM dbo.aglobal)

--IF ONE THEN IT IS FINANCIAL YEAR (APRIL -MARCH)
--IF TWO THE IT IS CALENDER YEAR (Jan-Dec)
IF @GlbLeaveYear = 1
BEGIN

set @FromDate ='01-Apr-'+Convert(varchar(90),@FromYear);

set @Todate ='31-Mar-'+Convert(varchar(90),@ToYear);

END
ELSE
BEGIN
set @FromDate ='01-Jan-'+Convert(varchar(90),@FromYear);

set @Todate ='31-Dec-'+Convert(varchar(90),@ToYear);

END

DECLARE @FKemapEmID AS INT =1
CREATE TABLE #empattendencecount
(
empid INT,
day DATE,
shiftid INT,
time_in DATETIME,
time_out DATETIME,
totalhours INT,
totalminutes INT,
fulldayhours INT
)
CREATE TABLE #EmpAttendence
(
employeeid INT,
Attendence_Date DATETIME ,ShiftID int
)

INSERT INTO #EmpAttendence
select FKemapEmID, CONVERT(DATETIME, CONVERT(CHAR(8), [apDate], 112)
+ ' ' + CONVERT(CHAR(8), [apTime], 108)),
FKShiftapShiftId
FROM [txnAcessPunches].[dbo].[atransAddPunch]
where [apDate] between @FromDate and @Todate and FKemapEmID= @FKemapEmID;

;WITH emp
AS (SELECT [EmployeeID],
[Attendence_Date],
CAST([Attendence_Date] AS DATE) AS [Day],
Row_Number()
OVER(
PARTITION BY [EmployeeID], CAST([Attendence_Date] AS DATE)
ORDER BY [EmployeeID], [Attendence_Date]) AS [RowNumber],ShiftID
FROM #EmpAttendence)

insert into #EmpAttendenceCount
SELECT t1.[EmployeeID] AS [EMPID],
t1.[Day],
Convert(varchar(90),t1.ShiftID) as 'ShiftID',
MIN(t1.[Attendence_Date]) AS [time_in],
MAX(t2.[Attendence_Date]) AS [time_out],
SUM(ISNULL(DATEDIFF(HH, t1.[Attendence_Date], t2.[Attendence_Date]), 0)) AS [TotalHours],
SUM(ISNULL(DATEDIFF(MM, t1.[Attendence_Date], t2.[Attendence_Date]), 0)) AS [TotalMinutes],
datepart(HH,S.shFullDayhrs )as 'FullDayHours'
FROM emp AS t1
LEFT JOIN emp AS t2
ON ( t1.[EmployeeID] = t2.[EmployeeID]
AND t1.[Day] = t2.[Day]
and t1.ShiftID=t2.ShiftID
AND t1.[RowNumber] = ( t2.[RowNumber] - 1 )
AND t2.[RowNumber] % 2 = 0 )
left join [ATS2014].[dbo].[amasterShift] as S
on Convert(varchar(90),S.aPKShift)= Convert(varchar(90),t1.ShiftID)
GROUP BY t1.[EmployeeID],
t1.[Day],t1.ShiftID,S.shFullDayhrs
having cast (Convert(varchar(90),SUM(ISNULL(DATEDIFF(HH, t1.[Attendence_Date], t2.[Attendence_Date]), 0)))+':'+ Convert(varchar(90),SUM(ISNULL(DATEDIFF(MM, t1.[Attendence_Date], t2.[Attendence_Date]), 0))) as time(7)) >=S.shFullDayhrs
ORDER BY t1.[EmployeeID],
t1.[Day];

select * from #empattendencecount

--select @@ROWCOUNT
drop table #EmpAttendence;
drop table #empattendencecount;

Andrius Leonavicius 20-May-14 7:46am

Change this line:
SUM(ISNULL(DATEDIFF(MM, t1.[Attendence_Date], t2.[Attendence_Date]), 0)) AS [TotalMinutes],
to this (difference in minutes):
SUM(ISNULL(DATEDIFF(MI, t1.[Attendence_Date], t2.[Attendence_Date]), 0)) AS [TotalMinutes],
or this (difference in minutes - hours):
SUM(ISNULL((DATEDIFF(MI, t1.[Attendence_Date], t2.[Attendence_Date]) % 60), 0)) AS [TotalMinutes],

Also, I can see the problem here:
Convert(varchar(90),SUM(ISNULL(DATEDIFF(MM, t1.[Attendence_Date], t2.[Attendence_Date]), 0)))
Try changing to this:
Convert(varchar(90),SUM(ISNULL((DATEDIFF(MI, t1.[Attendence_Date], t2.[Attendence_Date]) % 60), 0)))

By the way, why do you need two temporary tables?

surajemo 20-May-14 13:16pm

this table empattendencecount will give the count

and the comparison should be done by number of TotalMinutes right not by time .
please correct me if i am going wrong

having cast (Convert(varchar(90),SUM(ISNULL(DATEDIFF(HH, t1.[Attendence_Date], t2.[Attendence_Date]), 0)))+':'+ Convert(varchar(90),SUM(ISNULL(DATEDIFF(MM, t1.[Attendence_Date], t2.[Attendence_Date]), 0))) as time(7)) >=S.shFullDayhrs

Andrius Leonavicius 20-May-14 13:47pm

What data type is S.shFullDayhrs?

surajemo 20-May-14 13:53pm

time(7)

Andrius Leonavicius 20-May-14 14:04pm

OK, try this:
HAVING SUM(ISNULL(DATEDIFF(MI, t1.[Attendence_Date], t2.[Attendence_Date]), 0)) >= ISNULL(DATEDIFF(MI, 0, S.shFullDayhrs), 0)

surajemo 20-May-14 14:09pm

ok I will try it :) thank you

Andrius Leonavicius 20-May-14 14:13pm

You're welcome. Please let me how it works for you.

surajemo 21-May-14 1:20am

Worked Pretty well Thank you :)

Andrius Leonavicius 21-May-14 3:43am

I am glad to hear this.

surajemo 20-May-14 14:11pm

ok I will try it :) thank u