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,date
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... or ....
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.
Well anyways this is my idea any better solutions would be welcome.
(select emp_uid, to_date(emp_shift_duration