Query to Find Average Time for Worker

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 2000 Forums

SQL Server Development (2000)

Query to Find Average Time for Worker

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-09-28 : 17:18:44

THIS IS THE DATA

create table #durations
as (Contractor char(3), WORKERID int,Startdate datetime, Enddate datetime, SITE char(10))
insert #durations
values
ABC, 1, 1/1/2005, 1/31/2005, NYC1 union all
ABC, 1, 2/1/2005, 4/1/2005, NYC2 union all
XYZ, 2, 2/1/2006, 3/31/2006, NYC1 union all
XYZ, 2, 4/1/2006, 5/9/2007 , NYC union all
ABC, 3, 4/22/2005, null, nyc union all
ABC, 4, 1/1/2005, 1/31/2005, NYC1 union all
ABC, 4, 2/1/2005, 6/1/2005, NYC2

I need to find average no. of days a worker worked for each contractor
He may work on one site or may work on multiple sites as after working on one site he maybe transferred to another

If enddate > getdate() then (datediff(d, getdate(), min(startdate))

of that worker. He may have many start dates and many end dates. So we take the subtract his minimum start date from todays date.

If he has a end date which is less then today then we do this

datediff(max(enddate(), min(enddate()) of that worker

His last end date is subtract from his first start date

Say this is sample date

Contractor, WORKERID, Startdate, Enddate, SITE
ABC, 1, 1/1/2005, 1/31/2005, NYC1
ABC, 1, 2/1/2005, 4/1/2005, NYC2
XYZ, 2, 2/1/2006, 3/31/2006, NYC1
XYZ, 2, 4/1/2006, 5/9/2007 , NYC
ABC, 3, 4/22/2005, null, nyc
ABC, 4, 1/1/2005, 1/31/2005, NYC1
ABC, 4, 2/1/2005, 6/1/2005, NYC2

So for Worker 1
we will do
his max(enddate) 4/1/2005 - min(startdate) 1/1/2005

and we will take average of all workers like this. Similar is worker 4.

For worker 2 it since his end date is greate than today we will take todays date getdate()- min(startdate) 2/1/2006.

for worker 3 the date is null so we calculate his duration as
getdate() - start date

so we group by each contractor

say for ABC, 1 3 and 4 worked 100 days each
so average would be 100 (300/3)

Ashley Rhodes

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-28 : 17:57:06

Here is a query that should give you what you want along with a working version of your sample data.

create table #durations 
(Contractor char(3), WORKERID int,Startdate datetime, Enddate datetime, SITE char(10))
insert #durations
select 'ABC', 1, '1/1/2005', '1/31/2005', 'NYC1' union all
select 'ABC', 1, '2/1/2005', '4/1/2005', 'NYC2' union all
select 'XYZ', 2, '2/1/2006', '3/31/2006', 'NYC1' union all
select 'XYZ', 2, '4/1/2006', '5/9/2007' , 'NYC' union all
select 'ABC', 3, '4/22/2005', null, 'nyc' union all
select 'ABC', 4, '1/1/2005', '1/31/2005', 'NYC1' union all
select 'ABC', 4, '2/1/2005', '6/1/2005', 'NYC2'

SELECT contractor, avg(datediff(day, WorkerStartDate, WorkerEndDate)) AveragePerWorker
FROM
(SELECT contractor, workerid, min(startdate) WorkerStartDate, case when max(isnull(enddate, getdate())) > getdate() then getdate() else max(isnull(enddate, getdate())) end WorkerEndDate
FROM #durations
GROUP BY contractor, workerid) WorkerDates
GROUP BY contractor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources