opelraja
asked on
php mysql
I have an idea about keeping track of employee hours on a database: this database will be populated by the normal hours the employees work: therefore if someone is late or out the supervisor only has to go and edit the data and saves time from having to put hours in each day for everyone.
I was able to populate the data of one person on a table, call it Hours_worked:
fields: name; emp_id; account_uid; norm_hours_worked; date.
I wrote up an edit page in php and everything works fine, only problem is I cannot populate the table with more than one employee. There must be a better way to do this although I want to keep if possible the idea of pre-populating the data. I can make a table for each employee but just looking for better way.
I was able to populate the data of one person on a table, call it Hours_worked:
fields: name; emp_id; account_uid; norm_hours_worked; date.
I wrote up an edit page in php and everything works fine, only problem is I cannot populate the table with more than one employee. There must be a better way to do this although I want to keep if possible the idea of pre-populating the data. I can make a table for each employee but just looking for better way.
Since normal work hours is fixed make that available in a constant.
Just have a late hour table in which admin will input details of the employee who have come
late. lateid(auto generated id),EmpId,Date,latehours would be the field needed .
Of course you would need a run time script that would total late hours for a particular employee and deduct from the fixed normal hours constant to get the valid hours worked.
Just have a late hour table in which admin will input details of the employee who have come
late. lateid(auto generated id),EmpId,Date,latehours would be the field needed .
Of course you would need a run time script that would total late hours for a particular employee and deduct from the fixed normal hours constant to get the valid hours worked.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this is the sql I used to populate the dates: the rest of the data I put in as default values:
DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
WHILE dateStart <= dateEnd DO
INSERT INTO emp_hours_worked2 (_date) VALUES (dateStart);
SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
END WHILE;
END;
|
DELIMITER ;
CALL filldates('2013-01-01','20
there are various employees with different times worked during the day, not all work 6 hours, some 5, some 4.
I do have a table for employee information which does have a field : emp_id and hours_per_day. I realize I can 'relate' that table to a few standard tables which describe the possible hours worked.
hours_table:
hour_id, hour_total, ... being some data like : 01, 6
a little background: there is already a new time keeping system going in that is used by phone which it appears will be like the last one we used and a complete catastrophe: so these records are backups for me and also a way for me to see how many extra hours I will have on the weekend to do 'extra' work using the hours not fulfilled during the week because of lateness or people being absent. I figured if I have the table filled, if I then sum is up for each person during the pay period I will know each day how many hours extra I will have to use and can therefore plan ahead by askng folks if they are available.
so the question is ' I guess' using the two tables(employees) and (hours_table) how to create a third table based on the two tables that will generate the data I want.
DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
WHILE dateStart <= dateEnd DO
INSERT INTO emp_hours_worked2 (_date) VALUES (dateStart);
SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
END WHILE;
END;
|
DELIMITER ;
CALL filldates('2013-01-01','20
there are various employees with different times worked during the day, not all work 6 hours, some 5, some 4.
I do have a table for employee information which does have a field : emp_id and hours_per_day. I realize I can 'relate' that table to a few standard tables which describe the possible hours worked.
hours_table:
hour_id, hour_total, ... being some data like : 01, 6
a little background: there is already a new time keeping system going in that is used by phone which it appears will be like the last one we used and a complete catastrophe: so these records are backups for me and also a way for me to see how many extra hours I will have on the weekend to do 'extra' work using the hours not fulfilled during the week because of lateness or people being absent. I figured if I have the table filled, if I then sum is up for each person during the pay period I will know each day how many hours extra I will have to use and can therefore plan ahead by askng folks if they are available.
so the question is ' I guess' using the two tables(employees) and (hours_table) how to create a third table based on the two tables that will generate the data I want.
You can use the phone system data to maintain your records with a requirement for a supervisor to confirm the hours reported within the phone system.
Your basic setup as was pointed out would be
Employee table
Employeeid, name, other info specific to employee.
Hours worked table
Employeeid,date,hours_work
You can use starttime and stoptime as date time to deal with cross day handling, the only thing to be caution deals with handling day light savings.
You would define the date if used or the combination of starttime and endtime along with the employeeid to make sure there is no record being added where it is not possible if users will be adding their entires or as part of the data import from the phone system
I.e. a new shift can not start if an existing one is ongoing.
Employee1,2013-01-01 12:00:00 GMT as starttime
Employee1,2013-01-01 15:30:00 GMT as starttime.
Your basic setup as was pointed out would be
Employee table
Employeeid, name, other info specific to employee.
Hours worked table
Employeeid,date,hours_work
You can use starttime and stoptime as date time to deal with cross day handling, the only thing to be caution deals with handling day light savings.
You would define the date if used or the combination of starttime and endtime along with the employeeid to make sure there is no record being added where it is not possible if users will be adding their entires or as part of the data import from the phone system
I.e. a new shift can not start if an existing one is ongoing.
Employee1,2013-01-01 12:00:00 GMT as starttime
Employee1,2013-01-01 15:30:00 GMT as starttime.
ASKER
I re-asked the question under 'sql query', 'mysql' and I ended up figuring out what I wanted to do: I will paste how I asked the question in the other post so it may be better understood what I wanted to do.
I have a table called employees: within it I have various fields two of which being:
emp_uid, and emp_shift_duration.
emp_uid is unique.
emp_shift_duration varies a bit becasue most emps work either 4 hours, 5 hours, 6 hours or 8 hours.
Sometimes people are out and I want to use their hours to do special projects so I want to keep tabs on when they miss hours. The shift sup. can keep track of this but in order to save time I only want him to 'edit' the missing hours when needed, not add hours eveyday so I want the table populated ahead of time.
so I created another table which is called :
emp_hours_worked: fields are 3 : emp_uid, projected_hours,and date.
I wish to populate emp_hours_worked with employee data from employees table.
emp_uid, and emp_shift_duration; and also populate date with the days of the year for 'each' employee: this will be a very long table because the result will be that each employee will have a record for each date of the year.: therefore using a simple php script the sup. can go in and edit any necessary dates and then I will run a query to sum up the hours for the current pay weeks.
emp_hours_worked will look something like this.
emp_uid,projected_hours,da
3432,4,01-01-2013
3432,4,01-02-2013
3432,4,01-03-2013...etc.
7715,8,01-01-2013
7715,8,01-02-2013...etc...
3432,4,01-01-2013
7715,8,01-01-2013
9824,5,01-01-2013
9954,8.01-01-2013... etc. depending how its shown but did this to get the idea through.
So I am looking for sql statement to 'insert' all this data into the emp_hours_worked table.
**** this is how I solved it below ****
I created a table 'dates with just '_dates' as a field : being the whole year day to day of 2013.
then I used this query:
Insert into emp_hours_worked(emp_uid,p
SELECT emp_uid, emp_shift_duration,_date
FROM employees_table AS a,
date AS b;
So it populated a line for each employee for each day. Now I just need a faster laptop for this huge table.
I have a table called employees: within it I have various fields two of which being:
emp_uid, and emp_shift_duration.
emp_uid is unique.
emp_shift_duration varies a bit becasue most emps work either 4 hours, 5 hours, 6 hours or 8 hours.
Sometimes people are out and I want to use their hours to do special projects so I want to keep tabs on when they miss hours. The shift sup. can keep track of this but in order to save time I only want him to 'edit' the missing hours when needed, not add hours eveyday so I want the table populated ahead of time.
so I created another table which is called :
emp_hours_worked: fields are 3 : emp_uid, projected_hours,and date.
I wish to populate emp_hours_worked with employee data from employees table.
emp_uid, and emp_shift_duration; and also populate date with the days of the year for 'each' employee: this will be a very long table because the result will be that each employee will have a record for each date of the year.: therefore using a simple php script the sup. can go in and edit any necessary dates and then I will run a query to sum up the hours for the current pay weeks.
emp_hours_worked will look something like this.
emp_uid,projected_hours,da
3432,4,01-01-2013
3432,4,01-02-2013
3432,4,01-03-2013...etc.
7715,8,01-01-2013
7715,8,01-02-2013...etc...
3432,4,01-01-2013
7715,8,01-01-2013
9824,5,01-01-2013
9954,8.01-01-2013... etc. depending how its shown but did this to get the idea through.
So I am looking for sql statement to 'insert' all this data into the emp_hours_worked table.
**** this is how I solved it below ****
I created a table 'dates with just '_dates' as a field : being the whole year day to day of 2013.
then I used this query:
Insert into emp_hours_worked(emp_uid,p
SELECT emp_uid, emp_shift_duration,_date
FROM employees_table AS a,
date AS b;
So it populated a line for each employee for each day. Now I just need a faster laptop for this huge table.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for opelraja's comment #a38735253
for the following reason:
complete answer
Accepted answer: 0 points for opelraja's comment #a38735253
for the following reason:
complete answer
Your answer is basically a combination of all the suggestions and comments posted here when experts were trying to figure out what your setup is.
ASKER
Hi Arnold: this was my problem or my question as stated in my original submission:
"only problem is I cannot populate the table with more than one employee" ,
from all I saw in the answer/postings it was theory on how it could be done not on how to solve my problem, although some of the answers were helpful as theory if someone had the same problem I dont think they would solve it by reading the entries. I began with this original problem and really it was all about writing up the correct query which is why I re-wrote my problem and opened it under sql and mysql (only got one answer and actually just figured it out on the net). Anyways I have no problem doling out points for effort/contribution but I disagree that the posted comments collectively led to my answer. My resolution came by way of looking up a myriad of sql statements and working with examples until I got the result. I will think out my question delivery better next time and be more exact. I would agree overall that the postings helped to some extent so I would like points spread out amongst the contributors. Probably the most helpful posting was Ray's.
"only problem is I cannot populate the table with more than one employee" ,
from all I saw in the answer/postings it was theory on how it could be done not on how to solve my problem, although some of the answers were helpful as theory if someone had the same problem I dont think they would solve it by reading the entries. I began with this original problem and really it was all about writing up the correct query which is why I re-wrote my problem and opened it under sql and mysql (only got one answer and actually just figured it out on the net). Anyways I have no problem doling out points for effort/contribution but I disagree that the posted comments collectively led to my answer. My resolution came by way of looking up a myriad of sql statements and working with examples until I got the result. I will think out my question delivery better next time and be more exact. I would agree overall that the postings helped to some extent so I would like points spread out amongst the contributors. Probably the most helpful posting was Ray's.
ASKER
thanks to all
I did not get your problem exactly, but in your table hours_worked you can store data of more than one employee without any problems. you should be able to identify unique employee data via emp_id field.
Please put some more detail about your problem.
Hope this will help you.
Thank you.
Amar Bardoliwala