I have a query that calculates FTE by month. Each month consists of two pay periods. The worked hours are summed for the two pay periods and divided by the total possible hours to calculate the FTE.
The issue I am having is that some employees only have 1 pay period because they may have started mid-way through the month. How do I add an extra pay period into the master table.
For example, the chart belows shows how each employee should look in the table.
NamePay DayWorked HrsTotal Possible HrsJoe SmithOct-077575Joe SmithOct-216075135150FTE = 0.9
However, I have some employees that look like the following.
NamePay DayWorked HrsTotal Possible HrsJack LynnOct-0775757575FTE = 1.0
The FTE for Jack should be 0.5 because Oct -21 should be added with worked hours of 0 and total possible hours equal to the other pay period in that month. Keep in mind different employees can have different possible hrs. Is is not always 75.
Please help!!!
The issue I am having is that some employees only have 1 pay period because they may have started mid-way through the month. How do I add an extra pay period into the master table.
For example, the chart belows shows how each employee should look in the table.
NamePay DayWorked HrsTotal Possible HrsJoe SmithOct-077575Joe SmithOct-216075135150FTE = 0.9
However, I have some employees that look like the following.
NamePay DayWorked HrsTotal Possible HrsJack LynnOct-0775757575FTE = 1.0
The FTE for Jack should be 0.5 because Oct -21 should be added with worked hours of 0 and total possible hours equal to the other pay period in that month. Keep in mind different employees can have different possible hrs. Is is not always 75.
Please help!!!
