I've got data thus:
call_number	vet_datetime	vet_date	vetter	time_spent
165600	01-Dec-11	01-Dec-11	Mohammed Akbar	10
168688	01-Dec-11	01-Dec-11	Chris Brown	20
170463	01-Dec-11	01-Dec-11	Hitesh Patel	17
169104	01-Dec-11	01-Dec-11	Bob Smith	14
168215	01-Dec-11	01-Dec-11	Bob Smith	15
169237	01-Dec-11	01-Dec-11	Hitesh Patel	20
169104	01-Dec-11	01-Dec-11	Duncan Halfyard	14
169105	01-Dec-11	01-Dec-11	Hitesh Patel	15
I'm looking to figure out the utilization of the vetter but can't get my head around it.
What I want to do is take the number of vets performed that day, and knowing that I have 450 minutes of working time in a day, calculate their utilization - IE, 22 vets at 10 minutes each would be 220 minutes or approx 50% utilization.
I can do this now quite easily. However, over a period of a months data some staff are absent, so I need to calculate this whilst also considering dates where the vetter did NO vets.
IE, there a 450 minutes x 20 working days for everyone. Some staff might have only worked 10 days that month, so we cannot simply look at 9000 minutes available for everyone to calculate utilization. There must be a way to "count" the number of days where a vet took place, and from that point, we know the staff member was in, therefore "add" 450 minutes to their allowance, for want of a better expression.
Can anyone help??
Thanks in advance!