February 27, 2003 at 8:31 am
want to calculate the average hours worked
over a 17 week period rolling for the last year for each employee.
I use sql server 2000
hours worked are only shown daily.
so i first need to calculate the hours weekly and then go from there
for example, calculate the average hours worked from week 1 to week 17 for each employee
then calculate the average hours worked from week 2 to week 18 then
calculate the average hours worked from week 3 to week 19 and so on
February 28, 2003 at 4:07 am
Depends on whether you want the avg to be of 17 weeks or the number of weeks worked!
This code uses the data available and will calc the average of the weeks worked in the 17 week period.
If it is not what you require please supply more detail.
create table #wk (empno int,weekno int,hours int)
insert into #wk
select empno,DATEPART(week,[date]),sum(hours)
from tablea
where year([date]) = 2002
group by empno,DATEPART(week,[date])
select a.empno,a.weekno,avg(isnull(b.hours,0))
from #wk a
left outer join #wk b on b.empno = a.empno and b.weekno between (a.weekno - 16) and a.weekno
where a.weekno >= 17
group by a.empno,a.weekno
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply