I have a denormalized table in the following format:
ProjectID, EmployeeID, Position, Checked, Probability, fldwk01, fldwk02, fldwk03, fldwk04....> fldwk26.
The week fields hold estimated hours for the employee working on a project spanning the weeks.
I'm having a hard time using this table for charting report tools, so I think I need to normalize this data in a table in the following format:
ProjectID, EmployeeID, Position, Checked, Probility, Week_ending_date, hours.
I have a table called lkpcaptions that holds the actual dates for each week field:
The caption table gets updated every Friday. And that's when I would like to run, if possible, a stored procedure to build a table and populate it in the normalized format. The normalized table can be overwritten each week.
Would this be a good job for a Store Procedure? I'm not sure where to start. Would appreciate some pointers to get this done. Thanks!