SQL SUM DateTime Field

Hello Expert, I am making a sql statement. I have a table called tbl_Employee and tbl_TimeClock
I want to make a sql string that will get the total hours and minutes ( example 35 hours 20 minutes) each employee worked during the week.
tbl_Employee has fields
EmployeeID int
FirstName  nvarchar
LastName  nvarchar

tbl_TimeClock
EmployeeID int
StartTime  DateTIme
StopeTime DateTIme

Is there a way to do this?
jvoros1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BillAn1Connect With a Mentor Commented:
Sure :

SELECT sum(datediff(mi,StartTime ,StopeTime)) as total_minutes , firstName, LastName
FROM
tbl_Employee join
tbl_TimeClock
ON tbl_Employee.employeeID = tbl_TimeClock.employeeID
WHERE StartTime < getdate() - 7
GROUP BY firstName, LastName

you may need to modify the WHERE clause to whatever range of dates you want to pull data for.

If you want a string e.g. "35 hours 20 minutes", then do this :


SELECT cast(cast(sum(datediff(mi,StartTime ,StopeTime))/ 60 as integer) as varchar(4) + 'hours' + cast(cast(sum(datediff(mi,StartTime ,StopeTime)) % 60 as integer) as varchar(4)) + 'minutes' as total_time, firstName, LastName
FROM
tbl_Employee join
tbl_TimeClock
ON tbl_Employee.employeeID = tbl_TimeClock.employeeID
WHERE StartTime < getdate() - 7
GROUP BY firstName, LastName
0
 
hkamalCommented:
Of Course! With nicely formatted times;

SELECT e.EmployeeID, e.FirstName, e.LastName, select convert(varchar, DATEADD(mi, datediff(mi, e.StartTime, e.StopTime), "1-jan-04"), 108)
FROM tbl_Employee e, tbl_TimeClock t
WHERE e.EmployeeID=t.EmployeeID

BTW, I spotted a typo (StopeTime) which I changed to StopTime. If it was correct as is, change it back !


HTH
0
 
alexprestonCommented:
beat me to it!

I was just going to post...

select sum(datediff(mi,StartTime,StopTime)),EmployeeID from tbl_TimeClock
group by EmployeeID

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
BillAn1Commented:
Obviously I meant to have > getdate() - 7 !!

SELECT cast(cast(sum(datediff(mi,StartTime ,StopeTime))/ 60 as integer) as varchar(4) + ' hours ' + cast(cast(sum(datediff(mi,StartTime ,StopeTime)) % 60 as integer) as varchar(4)) + ' minutes' as total_time, firstName, LastName
FROM
tbl_Employee join
tbl_TimeClock
ON tbl_Employee.employeeID = tbl_TimeClock.employeeID
WHERE StartTime > getdate() - 7
GROUP BY firstName, LastName
0
 
alexprestonCommented:
hkamal, couple of mistakes in there!

SELECT e.EmployeeID, e.FirstName, e.LastName, convert(varchar, DATEADD(mi, datediff(mi, t.StartTime, t.StopTime), '1-jan-04'), 108)
FROM tbl_Employee e, tbl_TimeClock t
WHERE e.EmployeeID=t.EmployeeID
0
 
hkamalCommented:
Oops. missed the sum and Group by.
BillAn's solution is better for values above 24hrs (longer than most procs run!)
0
 
alexprestonCommented:
Agreed, BillAn's answer beat me to it and was better too!!
0
 
jvoros1Author Commented:
Thank you everyone for your help that is great :)
0
 
jvoros1Author Commented:
Is there a way to add a third table to this join I need to get chequeNo from tbl_EmployeeHours
I need the ChequeNo, Hours, LastName,FirstName
0
 
alexprestonCommented:
SELECT cast(cast(sum(datediff(mi,StartTime ,StopeTime))/ 60 as integer) as varchar(4) + 'hours' + cast(cast(sum(datediff(mi,StartTime ,StopeTime)) % 60 as integer) as varchar(4)) + 'minutes' as total_time, firstName, LastName, ChequeNo
FROM
tbl_Employee join
tbl_TimeClock
ON tbl_Employee.employeeID = tbl_TimeClock.employeeID
join
tbl_EmployeeHours
ON tbl_Employee.employeeID = tbl_EmployeeHours.employeeID
WHERE StartTime < getdate() - 7
GROUP BY firstName, LastName
0
 
jvoros1Author Commented:
Thank you so much that did it :)
0
 
jvoros1Author Commented:
The only thing is if my time is 7 min it doubles it and says 14 minutes because of the second join any suggestions ?
0
 
alexprestonCommented:
sounds like there might be multiple records for each employeeID in the tbl_EmployeeHours table? Is this the case? If it is then I'll try to think of a way around it.
0
 
jvoros1Author Commented:
Yes you are right Ok will try to think of  a way if you come up with one i would apprecaite it if you let me know
0
 
alexprestonCommented:
Why are there multiple records for each employeeID in the EmployeeHours table? Do they have different cheque numbers?
If they are different cheque numbers then just add ChequeNo to the GROUP BY clause.
e.g GROUP BY firstName, LastName, ChequeNo

If they are the same cheque numbers then you should create a VIEW on the EmployeeHours table. e.g.
CREATE VIEW vEmployeeHours AS
SELECT DISTINCT employeeID, ChequeNo FROM tbl_EmployeeHours

Then use vEmployeeHours instead of tbl_EmployeeHours in your SELECT statement.

hth,

Alex.
0
 
BillAn1Commented:
If there are multiple cheques, you might want to do something like this :

select total_time, firstName, LastName, chequeNo
FROM
(
SELECT cast(cast(sum(datediff(mi,StartTime ,StopeTime))/ 60 as integer) as varchar(4) + 'hours' + cast(cast(sum(datediff(mi,StartTime ,StopeTime)) % 60 as integer) as varchar(4)) + 'minutes' as total_time, firstName, LastName, tbl_Employee.employeeID
FROM
tbl_Employee join
tbl_TimeClock
ON tbl_Employee.employeeID = tbl_TimeClock.employeeID
WHERE StartTime < getdate() - 7
GROUP BY firstName, LastName ) nested

join
tbl_EmployeeHours
ON nested.employeeID = tbl_EmployeeHours.employeeID

This will give you multiple records if there are multiple cheques, each record will have the same total.
Is there a way to link specific cheques to certain time-cards etc?
0
All Courses

From novice to tech pro — start learning today.