Attendance Query

[Mandy J.S.]

Currently we have 2 tables that are being used for attendance data. The
first contains only those that have attendance issues: late, leave early,
absent and the other one that has everyone and how many days they worked for
that week. We were having a requirement of 90 days of 0 points. Now it is
90 days of 9 points AND work 15 days in a month.

Here's the current query for the old attendance:
SELECT tblEmployees.numIDNumber, tblEmployees.strLastName,
tblEmployees.strMiddleName, tblEmployees.strFirstName,
Max(tblAttendance.dtmDate) AS MaxOfdtmDate, tblEmployees.ynCurrentEmp,
tblEmployees.ynSalariedEmp, tblEmployees.dtmAnniveraryCompany
FROM tblIncidentType RIGHT JOIN (tblEmployees LEFT JOIN tblAttendance ON
tblEmployees.numIDNumber = tblAttendance.numID) ON
tblIncidentType.strIncidentType = tblAttendance.strIncidentType
GROUP BY tblEmployees.numIDNumber, tblEmployees.strLastName,
tblEmployees.strMiddleName, tblEmployees.strFirstName,
tblEmployees.ynCurrentEmp, tblEmployees.ynSalariedEmp,
tblEmployees.dtmAnniveraryCompany
HAVING (((Max(tblAttendance.dtmDate))<=DateAdd("d",-90,CDate([Enter the
ending date:]))) AND ((tblEmployees.ynCurrentEmp)=Yes) AND
((tblEmployees.ynSalariedEmp)=No) AND
((tblEmployees.dtmAnniveraryCompany)<=DateAdd("d",-120,CDate([Enter the
ending date:]))))
ORDER BY tblEmployees.numIDNumber, Max(tblAttendance.dtmDate) DESC;

This works great.
The new attendance table contains the following fields:

employeeID
Week Starting
Days Worked
Month

So, for each employee ID for 90 days I want to add up the days worked and
make sure they have 0 points. This is how we are calculating who gets an
attendance bonus. The problem with the 0 points is if someone takes all of
their vacation time in one month, thus getting 0 points, but also not being
at work either. We had a guy take all 3 weeks of his vacation in January.
So, he got an attendance bonus for being able to show up for 5 days in a row
to work. That wasn't exactly fair to the rest of the people that were here
that whole time.

I hope all this makes sense. I am figuring that the new query will have
them joined on the employee ID.

Thanks for the assistance.

 

[Chris2]

Currently we have 2 tables that are being used for attendance data. The
first contains only those that have attendance issues: late, leave early,
absent and the other one that has everyone and how many days they worked for
that week. We were having a requirement of 90 days of 0 points. Now it is
90 days of 9 points AND work 15 days in a month.

This works great.
The new attendance table contains the following fields:

employeeID
Week Starting
Days Worked
Month

So, for each employee ID for 90 days I want to add up the days worked and
make sure they have 0 points. This is how we are calculating who gets an
attendance bonus. The problem with the 0 points is if someone takes all of
their vacation time in one month, thus getting 0 points, but also not being
at work either. We had a guy take all 3 weeks of his vacation in January.
So, he got an attendance bonus for being able to show up for 5 days in a row
to work. That wasn't exactly fair to the rest of the people that were here
that whole time.

I hope all this makes sense. I am figuring that the new query will have
them joined on the employee ID.

Thanks for the assistance.

Mandy J.S.,

SELECT tblEmployees.numIDNumber
,tblEmployees.strLastName
,tblEmployees.strMiddleName
,tblEmployees.strFirstName
,Max(tblAttendance.dtmDate) AS MaxOfdtmDate
,tblEmployees.ynCurrentEmp
,tblEmployees.ynSalariedEmp
,tblEmployees.dtmAnniveraryCompany
FROM tblIncidentType
RIGHT JOIN
(tblEmployees
LEFT JOIN
tblAttendance
ON tblEmployees.numIDNumber
= tblAttendance.numID)
ON tblIncidentType.strIncidentType
= tblAttendance.strIncidentType
GROUP BY tblEmployees.numIDNumber
,tblEmployees.strLastName
,tblEmployees.strMiddleName
,tblEmployees.strFirstName
,tblEmployees.ynCurrentEmp
,tblEmployees.ynSalariedEmp
,tblEmployees.dtmAnniveraryCompany
HAVING (((Max(tblAttendance.dtmDate))
<= DateAdd("d",-90,CDate([Enter the ending date:])))
AND ((tblEmployees.ynCurrentEmp) = Yes)
AND ((tblEmployees.ynSalariedEmp) = No)
AND ((tblEmployees.dtmAnniveraryCompany)
<= DateAdd("d",-120,CDate([Enter the ending date:]))))
ORDER BY tblEmployees.numIDNumber
,Max(tblAttendance.dtmDate) DESC;


A WHERE clause operates on data before the GROUP BY begins.

A HAVING clause operates on data after the GROUP BY is performed.

The HAVING clause specifies all non-salaried current employees. The
question is, why aren't these conditions specified in a WHERE (missing
entirely from this query), thus reducing hte work needed to perform
the GROUP BY?


As for your question, you did not specify what end-result you wanted
to accomplish as result of the "fairness" issue caused by the one
employee taking all those consecutive weeks off.

I would like to point out that any action you take in that direction
will *penalize* your employees for taking vacation time.

Do you really want to go down that road? It'll generate a
substantially larger load of complaints, I think, than any "fairness"
issue caused by someone's vacation time block making it easy to score
"zero" points.

In any event, you mention that the policy has changed, and now up to 9
points can be had in 90 days.

I'm confused. Can you please detail exactly what you wish to
accomplish?

Oh, and I'm going to copy my standard pre-written requests below.

Please provide the DDL (including CONSTRAINTS)
for all of the Tables involved. If the DDL is
not available, please include a well-formatted
text description (monospace-font) of the
relevant portions of the structures of each
Table, including a description of the Primary
and Foreign Keys (i.e. "relationships").
Please also include some sample data from each
table (enough rows from each table to allow
any needed Queries to be executed). Please
also include the expected output.


Sincerely,

Chris O.

 

[Mandy J.S.]

Chris,

Let me try to explain this more clearly.

Basically I want to know how to get a query to add up how many days in a
month that someone has worked. These are entered into the table weekly, so
there will be 4 or 5 lines for each employee. This is done only for hourly
people, as they are the only ones that get attendance bonuses.

The fields available in the table are:

numID - number (this is the employee ID #, never reused and this is looked
up in tblEmployees)
dtmWeekStarting - Date/Time (the week's Monday date)
numDaysWorked - number (increments of ½ hours, 1 decimal place and uses look
up table - tblDays)
txtMonth - text (which month the week goes with)

Our plant generally works 5 or 6 days a week, so they have between 20 & 30
work days a month. There is plenty of time for them to attain 15 days a
month even with vacation and personal time. We want them to get an
attendance bonus for actually being here, not taking a bunch of vacation time
in order to get "perfect" attendance. They are allowed "0" attendance points
during the 90 day period. The period ends on the last day of month. It is
intended to reward those that show up every day. We are up to almost 40
employees a month that receive awards. We have 101 hourly employees that are
eligible.

For your question about the "having" vs "where" clause. I'm unclear as to
what the issue is.

Thanks for your help.

Currently we have 2 tables that are being used for attendance data. The
first contains only those that have attendance issues: late, leave early,
absent and the other one that has everyone and how many days they worked for
that week. We were having a requirement of 90 days of 0 points. Now it is
90 days of 9 points AND work 15 days in a month.

This works great.
The new attendance table contains the following fields:

employeeID
Week Starting
Days Worked
Month

So, for each employee ID for 90 days I want to add up the days worked and
make sure they have 0 points. This is how we are calculating who gets an
attendance bonus. The problem with the 0 points is if someone takes all of
their vacation time in one month, thus getting 0 points, but also not being
at work either. We had a guy take all 3 weeks of his vacation in January.
So, he got an attendance bonus for being able to show up for 5 days in a row
to work. That wasn't exactly fair to the rest of the people that were here
that whole time.

I hope all this makes sense. I am figuring that the new query will have
them joined on the employee ID.

Thanks for the assistance.

Mandy J.S.,

SELECT tblEmployees.numIDNumber
,tblEmployees.strLastName
,tblEmployees.strMiddleName
,tblEmployees.strFirstName
,Max(tblAttendance.dtmDate) AS MaxOfdtmDate
,tblEmployees.ynCurrentEmp
,tblEmployees.ynSalariedEmp
,tblEmployees.dtmAnniveraryCompany
FROM tblIncidentType
RIGHT JOIN
(tblEmployees
LEFT JOIN
tblAttendance
ON tblEmployees.numIDNumber
= tblAttendance.numID)
ON tblIncidentType.strIncidentType
= tblAttendance.strIncidentType
GROUP BY tblEmployees.numIDNumber
,tblEmployees.strLastName
,tblEmployees.strMiddleName
,tblEmployees.strFirstName
,tblEmployees.ynCurrentEmp
,tblEmployees.ynSalariedEmp
,tblEmployees.dtmAnniveraryCompany
HAVING (((Max(tblAttendance.dtmDate))
<= DateAdd("d",-90,CDate([Enter the ending date:])))
AND ((tblEmployees.ynCurrentEmp) = Yes)
AND ((tblEmployees.ynSalariedEmp) = No)
AND ((tblEmployees.dtmAnniveraryCompany)
<= DateAdd("d",-120,CDate([Enter the ending date:]))))
ORDER BY tblEmployees.numIDNumber
,Max(tblAttendance.dtmDate) DESC;


A WHERE clause operates on data before the GROUP BY begins.

A HAVING clause operates on data after the GROUP BY is performed.

The HAVING clause specifies all non-salaried current employees. The
question is, why aren't these conditions specified in a WHERE (missing
entirely from this query), thus reducing hte work needed to perform
the GROUP BY?


As for your question, you did not specify what end-result you wanted
to accomplish as result of the "fairness" issue caused by the one
employee taking all those consecutive weeks off.

I would like to point out that any action you take in that direction
will *penalize* your employees for taking vacation time.

Do you really want to go down that road? It'll generate a
substantially larger load of complaints, I think, than any "fairness"
issue caused by someone's vacation time block making it easy to score
"zero" points.

In any event, you mention that the policy has changed, and now up to 9
points can be had in 90 days.

I'm confused. Can you please detail exactly what you wish to
accomplish?

Oh, and I'm going to copy my standard pre-written requests below.

Please provide the DDL (including CONSTRAINTS)
for all of the Tables involved. If the DDL is
not available, please include a well-formatted
text description (monospace-font) of the
relevant portions of the structures of each
Table, including a description of the Primary
and Foreign Keys (i.e. "relationships").
Please also include some sample data from each
table (enough rows from each table to allow
any needed Queries to be executed). Please
also include the expected output.


Sincerely,

Chris O.

 

[Chris2]

Chris,

Let me try to explain this more clearly.

Basically I want to know how to get a query to add up how many days in a
month that someone has worked. These are entered into the table weekly, so
there will be 4 or 5 lines for each employee. This is done only for hourly
people, as they are the only ones that get attendance bonuses.

The fields available in the table are:

numID - number (this is the employee ID #, never reused and this is looked
up in tblEmployees)
dtmWeekStarting - Date/Time (the week's Monday date)
numDaysWorked - number (increments of ½ hours, 1 decimal place and uses look
up table - tblDays)
txtMonth - text (which month the week goes with)

Our plant generally works 5 or 6 days a week, so they have between 20 & 30
work days a month. There is plenty of time for them to attain 15 days a
month even with vacation and personal time. We want them to get an
attendance bonus for actually being here, not taking a bunch of vacation time
in order to get "perfect" attendance. They are allowed "0" attendance points
during the 90 day period. The period ends on the last day of month. It is
intended to reward those that show up every day. We are up to almost 40
employees a month that receive awards. We have 101 hourly employees that are
eligible.

Mandy J.S.,

That's a lot more information, however, it's not yet enough (my
apologies). See below.

For your question about the "having" vs "where" clause. I'm unclear as to
what the issue is.

Processing speed. But with such small tables, it probably won't
matter.


Let's see, how to explain?

I know your current query is working, but not *exactly* the way you
want it, because it shows people qualifying for the bonus when they've
taken big chunks of vacation time, and didn't have to get themselves
into work in order to earn it.

My quesiotn is, what is the *exact* alteration to your policy that
forbids this?

"If an employee takes more than one week of vacation during the 90 day
period, those vacation days days beyond the week cause the employee to
accumulate Attendence Points?" <--A very nasty policy, if I do say so.

"If an employee takes vacation time during the 90 period, they cannot
qualify for the bonus?" <--If an employee takes a day off, their
incentive to get themselves into work for the next 90 days is gone.

There are many possibilities, and I need to know *exactly* what the
*new* policy is (that prevents employees from taking advantage of
their vacation time) in order to successfully alter your query.


Sincerely,

Chris O.