Solved Forecast - Is employee on par to work 80 hours for the month

[motleyjew]

Good Morning,

I am trying to determine if a list of employees are on par to work at least 80 hours for the month. I have my table below called Labor_Insights. It has Agent_ID, Date_Worked, Staffed_Time(in seconds) fields. Below is an example of just one employee for a full month.

I normally run this data after my month ends. If sum(staffed time in seconds)/60/60 > 80 then they are eligible to be on the report. The sum below for employee 5 is 319,384. (319384/60/60) comes out to 88.71. So they would be eligible.

I am now running this data on a daily basis. I am looking to find a way in a access query to determine if the employee is on target to being at least 80 hours for the current month that is in progress (fiscal month range is 3/29 to 4/28). So if the below table for employee 5 only had 3 days worth of data for 3/31, 4/1, 4/2, that would total to 64,454 I'd like to say they are on par for working 80 hours based on some type of forecasting formula. Appreciate any help.

Thanks
Gregg

Table Labor_Insights

Agent _ID	Date_Worked	Staffed_Time
5​	3/31/2020​	30,656​
5​	4/1/2020​	3,367​
5​	4/2/2020​	30,431​
5​	4/3/2020​	29,319​
5​	4/7/2020​	30,591​
5​	4/8/2020​	30,524​
5​	4/9/2020​	13,043​
5​	4/10/2020​	12,044​
5​	4/13/2020​	30,908​
5​	4/14/2020​	30,685​
5​	4/15/2020​	6,213​
5​	4/20/2020​	5,666​
5​	4/22/2020​	30,435​
5​	4/23/2020​	7,152​
5​	4/28/2020​	28,350​

 

[plog]

1. You need a better dataset. To test this you are going to need data of a month that hasn't ended yet. So your April data isn't of much use, you should take the data you posted, add 1 month to every date you provided and then delete every date that hasn't occured yet.

2. You need a table to define your "fiscal months": ID, FiscalMonth, FiscalYear, StartDate, EndDate.

3. Build a query on that new table that produces 1 record which identifies the curent month (let's call this sub1).

4. That query should also have a calculated field which determines the total number of seconds needed to be on track for 80 hours up to that point. I think the best way to do this is:

OnTrackSeconds: ((CurrentDate - StartDate)/(EndDate - StartDate))*(80*60*60)

5. Your final query will JOIN sub1 to Labor_Insights:

FROM Labor_Insights INNER JOIN sub1 ON Date_Worked>=StartDate AND Date_Worked<=EndDate

6. Your final query will SUM Staffed_Time and compare it to OnTrackSeconds to determine if the Agent is on track.

 

[Deleted member 147267]

Not my best type of challenge, but I think there's some info missing if what you're after is suggestions for a calculation.
I think the number of possible working days in a month is needed, and if that varies from a month with 28 to 31 days, then I'm not sure I'd want to tackle it. If it's based on say, 20 days out of 28 are possible working days, then your example suggests 18 hours have been worked over 3 days. If that is extrapolated over 20 working days (4 weeks of 5 days each) then I get 119 at that rate. Don't know if that helps.
64,454/60/60 is just under 18 over 3 days
18/3 = x/20
3x = 18*20
x = (18x20)/3
= 119

If that's too limited, you need a lot of supporting data and calculations to deal with variable inputs.

 

[motleyjew]

Thank you both for the detailed solutions. I think the example Micron gave is what I needed to wrap my head around. I can use your examples to test past months to see what adjustments need to be made. Thanks you both again for your help!

Gregg

 

[Deleted member 147267]

You're welcome! Let us know how you make out and good luck with your db.