complex filter

[SD4054]

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.

 

[Duane Hookom]

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.

 

[SD4054]

I have at least 1 date entered for every EmpID. It may not be in the last 30
days, but there is 1.

 

[Duane Hookom]

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.

 

[SD4054]

Sorry, I did have one but I forgot to include it.
tblOvertime VOTNum, EmpID,VOTDate, ShiftID, VOTHrs

 

[Duane Hookom]

"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;

 

[SD4054]

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.

 

[SD4054]

Is this going to show who has not worked OT in the last 30 days, or just the
count of who has worked?

 

[SD4054]

This worked but did not show the employees that have not worked ot in the
last 30 days.

 

[Duane Hookom]

You may need to add " Or Is Null" with the <24 under that particular column.

 

[SD4054]

It all appears to work perfectly. Thank You!

You may need to add " Or Is Null" with the <24 under that particular column.