Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL SUM DateTime Field

Posted on 2004-08-18
16
Medium Priority
?
3,252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

721 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