Solved

SQL SUM DateTime Field

Posted on 2004-08-18
16
3,220 Views
Last Modified: 2008-03-10
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?
0
Comment
Question by:jvoros1
  • 6
  • 5
  • 3
  • +1
16 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 total points
ID: 11830076
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
 
LVL 5

Expert Comment

by:hkamal
ID: 11830135
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
 
LVL 3

Expert Comment

by:alexpreston
ID: 11830136
beat me to it!

I was just going to post...

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

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 17

Expert Comment

by:BillAn1
ID: 11830150
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
 
LVL 3

Expert Comment

by:alexpreston
ID: 11830156
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
 
LVL 5

Expert Comment

by:hkamal
ID: 11830175
Oops. missed the sum and Group by.
BillAn's solution is better for values above 24hrs (longer than most procs run!)
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11830190
Agreed, BillAn's answer beat me to it and was better too!!
0
 

Author Comment

by:jvoros1
ID: 11830414
Thank you everyone for your help that is great :)
0
 

Author Comment

by:jvoros1
ID: 11830581
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
 
LVL 3

Expert Comment

by:alexpreston
ID: 11830606
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
 

Author Comment

by:jvoros1
ID: 11830786
Thank you so much that did it :)
0
 

Author Comment

by:jvoros1
ID: 11831030
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
 
LVL 3

Expert Comment

by:alexpreston
ID: 11831083
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
 

Author Comment

by:jvoros1
ID: 11831161
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
 
LVL 3

Expert Comment

by:alexpreston
ID: 11831259
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 11831486
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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now