Calculate Time Duration Query - 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 Time Duration Query

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2009-11-02 : 01:37:08

Hi all,

I have a task to calculate time duration for timesheet records. Please run the SQL queries below to create table & insert records.

Please help to advise if it is easier to calculate using third party program or can be done using SQL query.

I'm also interested to know whether it is possible to get all distinct periods and then we compare to each records to get the duration (*you'll understand if you draw a diagram to illustrate the records)

Thank so much.

Regards,
Erwine

CREATE TABLE TIMESHEET
(
Job nvarchar(10),
StartDate datetime,
StartTime datetime,
EndDate datetime,
EndTime datetime
)

Insert into Timesheet
Select 'JobA','20091010',CAST('10-10-2009 10:00:00' AS DATETIME),'20091010', CAST('10-10-2009 11:00:00' AS DATETIME) UNION
Select 'JobB','20091010',CAST('10-10-2009 10:20:00' AS DATETIME),'20091010', CAST('10-10-2009 10:40:00' AS DATETIME) UNION
Select 'JobC','20091010',CAST('10-10-2009 10:30:00' AS DATETIME),'20091010', CAST('10-10-2009 11:30:00' AS DATETIME)

I would like to get a result:

# Job Duration
-------------------
1. JobA 38.3 mins
2. JobB 10.0 mins
3. JobC 43.3 mins

Formula:
Job A = (20 + (10/2) + (10/3) + (20/2)) mins
= 38.3 mins

Explanation:
All the jobs are done by one person, let's say Technician1. Because there are times when he did multi-tasking at the same time, it is possible to have overlapping period. This period must be divided based on the no. of jobs performed during the duration.

For JOB A:
From 10.00 to 10.20 = 20mins (Job A)
From 10.20 to 10.30 = 10mins / 2 (Job A and B)
From 10.30 to 10.40 = 10mins / 3 (Job A, B and C)
from 10.40 to 11.00 = 20mins / 2 (Job A and C)
TOTAL = 38.3 mins

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-02 : 02:19:44

[code]
; with tm
as
(
select [Time] = dateadd(minute, n.NUMBER, StartTime),
cnt = 1.0 / count(*)
from TIMESHEET t
cross apply F_TABLE_NUMBER_RANGE(0, datediff(minute, StartTime, EndTime)) n
group by dateadd(minute, n.NUMBER, StartTime)
)
select t.Job, ag = convert(decimal(10,2), sum(cnt))
from TIMESHEET t
inner join tm on t.StartTime <= tm.[Time]
and t.EndTime >= tm.[Time]
group by t.Job
[/code]

KH
[spoiler]Time is always against us[/spoiler]

erwine
Yak Posting Veteran

69 Posts

Posted - 2009-11-02 : 21:58:49

Hi Mr.Tan,

Thanks for replying.

I run the script and it returns:

JobA 38.67
JobB 8.67
JobC 43.67

I'm not sure if the result is correct since JobA supposed to only consume 38.3 minutes.
The results based on my manual calculation:

JobA 38.33
JobB 8.33
JobC 43.33

I also tried executing only this part:

select [Time] = dateadd(minute, n.NUMBER, StartTime),
cnt = 1.0 / count(*)
from TIMESHEET t
cross apply F_TABLE_NUMBER_RANGE(0, datediff(minute, StartTime, EndTime)) n
group by dateadd(minute, n.NUMBER, StartTime)

and it returns me different sets of records randomly. Any explanations, Tan?

Thanks again.

Regards,
Erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-02 : 22:03:07

your calculation assumed 10.20 to 10.30 is 10 minutes where as my query calculate it as 11 minutes.

10:20, 10:21 - 10.30 => total 11 mintues