calculate total time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

calculate total time

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-10-23 : 09:06:55

Hi

I have a table that have a structure like this..


ID   DateAdded      DateTimeIn                DateTimeOut
1    2010-10-22     2010-10-22 08:00:00       2010-10-22 10:00:00
1    2010-10-22     2010-10-22 10:30:00       2010-10-22 14:00:00
1    2010-10-23     2010-10-22 07:00:00       2010-10-22 18:00:00

The table hold records of when I started working and when I ended work on a specific day. I would like to calculate the amount of time I have worked for each day. Can someone give me any pointers?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-23 : 09:14:36

SELECT DateAdd(day, DateDiff(day, 0, DateTimeIn), 0) Date,
SUM(DateDiff(minute, DateTimeIn, DateTimeOut)) Minutes
FROM myTable
GROUP BY DateAdd(day, DateDiff(day, 0, DateTimeIn), 0)

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-10-23 : 15:52:58

Excellent, Thanks!

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-10-24 : 00:40:50

A Bit more readable

DECLARE @Table TABLE
(
ID INT,
DateAdded datetime,
DateTimeIn datetime,
DateTimeOut datetime
)
INSERT INTO @Table
SELECT 1,'2010-10-22','2010-10-22 08:00:00','2010-10-22 10:00:00' UNION ALL
SELECT 1,'2010-10-22','2010-10-22 10:30:00','2010-10-22 14:00:00' UNION ALL
SELECT 1,'2010-10-23','2010-10-22 07:00:00','2010-10-22 18:00:00'

SELECT convert(varchar,DateAdded,107) as 'Date',
substring(cast(DateTimeIn as varchar),12 ,len(cast(DateTimeIn as varchar))) as 'Clocked In',
substring(cast(DateTimeOut as varchar),12 ,len(cast(DateTimeOut as varchar))) as'Clocked out',
DateDiff(minute, DateTimeIn, DateTimeOut)as 'minutes'
from @Table
order by Dateadded
compute sum(DateDiff(minute, DateTimeIn, DateTimeOut))
--------------------------------

(3 row(s) affected)
Date Clocked In Clocked out minutes
------------------------------ ------------------------------ ----
Oct 22, 2010 8:00AM 10:00AM 120
Oct 22, 2010 10:30AM 2:00PM 210
Oct 23, 2010 7:00AM 6:00PM 660

sum
-----------
990

(4 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources