Tricky Stored Procedure and UDF for timesheet app

SQL Apprentice Question
I am trying to develop a stored procedure and UDF which will create a
holding table to allow
the users of my application to store their timesheets for the current
week and was looking
for some ideas as to how to best put it together.

The stored procedure will need to:


1. Take as input the end day for the current week
2. Generate a new table from the cartesian
product of an employees table and a user
defined function or other means of generating
the dates encompassing the given date, going back 7 days


The table will look like this when first generated


ID Date Employee Name Job No Division
Hours Category
PRIMARY INDEX Generated from udf From employees Blank Blank
Blank Blank


The thing is that I am really struggling with the definition of the UDF
and stored procedure to make this happen.


Can anyone point me in the right direction here?



Celko Answers

>> I am trying to develop a stored procedure and UDF which will create a holding table to allow the users of my application to store their timesheets for the current week <<


Why mimic a paper form in the database? Instead of thinking in
procedural terms, think of using a calendar table (delcarative
relational programming) with a week number column in it. What is that
vague magical universal id column for? You should have a natural key
that needs enforcement. Likewise, your other data element names are
eirther reserved words or too vague.

CREATE TABLE Timesheets
(work_date DATETIME DEFAULT CURRENT_TIMESTAMP
emp_name VARCHAR(35) NOT NULL
REFERENCES Personnel (emp_name),
job_nbr INTEGER NOT NULL
REFERENCES Jobs (job_nbr),
company_division INTEGER NOT NULL,
work_hrs DECIMAL (5,2) DEFAULT 0.00 NOT NULL
CHECK (work_hrs >= 0.00),
job_category INTEGER NOT NULL, -- or is it empl_category? or what?
PRIMARY KEY (work_date, emp_name, ,job_nbr));


The job category should be in the Jobs table; personnel category should
be in the Personnel table. What is this vague category?



>> 2. Generate a new table from the cartesian product [CROSS JOIN] of Personnel table and a user defined function or other means of generating the dates encompassing the given date, going back 7 days <<


You do not go around creating tables on the fly. Especially not tables
with the same structure. That is a non-normal form redundancy and
attribute splitting. You are thinking in terms of scratch tapes,
procedural code and paper forms, and not relational tables.

Just insert the data from the paper forms into the Timesheet table.
You can then do a LEFT OUTER JOIN on the calendar table to fill in the
missing days with with zero hours and a job of "not working" in a
stored procedure or a VIEW.