complex filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to track Overtime. I need to be able to Order an employee to
work OT based on the number of times they have worked OT in the last 30 days.
I need to sort by lowest number of days worked and then by their rank, which
is the year they were hired and their number, E.g. 2006010. I also can’t
allow an employee to work more than 24 hours of OT in the current week.
I have 3 tables

tblEmployee EmpID, LName, FName,VotedSlot, PhysicalLocation
tblOvertime VOTNum, EmpID, ShiftID, VOTHrs
tblShift VotedSlot, Shift, S, M, T, W, R, F, Sa

I have 5 shifts with varying days off on each shift. I can figure out who
is here on a specific day and shift, but I can’t figure out the rest.
 
There is nothing evident in your tables that suggest EmpID worked on any
particular date that caused overtime.
tblShift looks un-normalized but it may be working for or against you.
 
I have at least 1 date entered for every EmpID. It may not be in the last 30
days, but there is 1.
 
You didn't understand my comments. You don't have a date field that any of
us are aware of. Without a date field, you can't track overtime during the
last 30 days.
 
Sorry, I did have one but I forgot to include it.
tblOvertime VOTNum, EmpID,VOTDate, ShiftID, VOTHrs
 
"year they were hired and their number" wasn't provided either :-(
This should get you started:

SELECT (SELECT Count(*) FROM tblOverTime ot1 WHERE VOTDate >= Date()-30 AND
ot1.EmpID = tblEmployee.EmpID) as NumRecentOTs, (SELECT Sum(VOTHrs) FROM
tblOverTime ot2 WHERE VOTDate >= Date()-7 AND ot2.EmpID = tblEmployee.EmpID)
as SumWeekOTHrs, tblEmployee.*
FROM tblEmployee
WHERE (SELECT Sum(VOTHrs) FROM tblOverTime ot2 WHERE VOTDate >= Date()-7 AND
ot2.EmpID = tblEmployee.EmpID)<24
ORDER BY 1;
 
Thank you, that looks great, but I won't be able to try it until Friday
night. "year they were hired and their number" is their EmpID, "2006010".
Sorry.
 
Is this going to show who has not worked OT in the last 30 days, or just the
count of who has worked?
 
Back
Top