Design table - keeping worker status

[snoopy2003]

Hello,

I have an employee table:
ID
firstName
LastName

and a jobs table:
ID
JobID
effectiveDate

Each employee has status - Active/Inactive.

Where do you recommend to store this data for each worker ?
(I will need to retrieve monthly report of active/in active workers).



I thought about workersStatus table
ID
effectiveDate
status

but I am not sure it is sufficient.

Thank you for your help

[TheShabz]

it depends on what the status is supposed to signify. Status in the company as in current employee or former employee? status on a particular job? If the former, a simple yes/no field in employee [IsActive] should suffice. If the latter, you will need a third table to facilitate a many-to-many relationship between job and employee, as each employee over time can be part of many jobs and each job can have many employees.

so you would keep your tables as they are and create a third table
tblEmployeeJob
employeejobID(pk autnum)
empID (fk)
jobID(fk)
isActive (yes/no)

with this you can query a specific employee and see all jobs they were ever a part of as well as see which one they are currently a part of.

Hope this helps.

[snoopy2003]

Hello,

Thank you for your answer.

I need both type of data - current/former employee and status on a particular job, so I need to add the field 'active/not active' to both tblEmployee and tblEmployeeJob?

I do not understand how can I get monthly data afterward.

I mean: If I wish to know the number of active workers per month for the last year. How this data can be retrieved from these tables ?

Thank you for your help

[TheShabz]

1. yes you would put isActive field in both tables, but each would signify different things
2. your Job table would include a start and end date for the job. your employeeJob table would have a start and end date for that employee's time as a member of the job team. assuming every record in employeeJob starts with an employee being an active member, we can conclude that any employee that had a start or end date in the month in question would at some point be an active member of the job, regardless of the current status.
the sql would look something like

SELECT Count(empID)
FROM tblEmployeeJob
GROUP BY empID
HAVING (startDate BETWEEN [first date of month] AND [last date of month]) OR endDate BETWEEN [first date of month] AND [last date of month]

That will give you how many employees were active at some point during the given month. Further, you can get a list of all employees currently on a job just by using the isActive criteria.

[snoopy2003]

Hello,

Thank you for the detailed answer.
I tried to execute the query but the "Having" caused error.
I changed it to "where" (I do not know if it equals in this case).

I do not understand:
If I have record in tblEmployeeJob:
employeeID = 1
startDate = 1/1/2011
endDate = 5/1/2011
jobID = 1
status = active

and I wish to count Employees from 2/1/2011 till 28/2/2011, he will not count as active employee in 2/2011 because 1/1/2011, 5/1/2011 are not between these dates (2/1/2011 till 28/2/2011).

Do you have any suggestion in order to solve it ?

Thank you for your help

[TheShabz]

Add an OR status = "active" <-ignore that

make it:
((startDate >= [the date] AND endDate <= [the date]) OR
(startDate <= [the date] AND endDate >= [the date]) OR
(startDate <= [the date] AND endDate >= [the date]))


The HAVING is essentially a WHERE used in conjuction with GROUP BY. For testing purposes, remove the GROUP BY clause and change HAVING to WHERE.

[snoopy2003]

Thank you for your help. It works.

I just need to add another query to generate list of months (user input - from month till month) and run this query as sub query.

Thank you

[TheShabz]

I don't think it's quite done yet. The reason I did a GROUP BY originally was because of the possibility that an employee ends one job and begins another in the same month. This would give you 2 different records for the same employee. You said you wanted to know "how many" employees worked. This would throw off your count. I would run another query to do a count on the group by, something like:
SELECT Count(empID)
FROM tableName
GROUP BY empID;

[snoopy2003]

Thank you for your answer and for your help