Solved

SQL SUM DateTime Field

Posted on 2004-08-18
16
3,234 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 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
store vs query adhoc - no show rows 4 37
What is needed to become a DBA? 7 52
HIghlights of SSIS? 3 42
Creating Scalar Function 3 17
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

732 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