Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL SUM DateTime Field

Posted on 2004-08-18
16
Medium Priority
?
3,257 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

773 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