asked on

Store Procedure to build norm table from Denorm table

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:
fldFieldno fldcaption
----------- ------------
fldwk01 07/13/07
fldwk02 07/20/07
fldwk03 07/27/07

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!

mrichmon

>>Would this be a good job for a Store Procedure

Yes or a SISS package (or DTS if SQL 2000).

We actually have to do this nightly. We have a datasource that is denormalized, but we need to pull, and store normalized. Ours is a bit different than yours. It looks like this:

Username codes
joe 123,245,677,337

And we need to turn into

joe 123
joe 245
joe 677
joe 337

etc..

Anyway, you can do it by something like

SELECT 'fldwk01' AS fldFieldno, fldwk01 AS fldcaption
FROM yourtable
WHERE fldwk01 IS NOT NULL
UNION
SELECT 'fldwk02' AS fldFieldno, fldwk02 AS fldcaption
FROM yourtable
WHERE fldwk02 IS NOT NULL
UNION
etc...

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

To run it automatically, create a job with one SQL step that empties then reloads the table:

BEGIN TRANSACTION
TRUNCATE TABLE normalized
IF @@ERROR <> 0
BEGIN
...error processing...
ROLLBACK TRANSACTION
GO TO exitCode
END --IF
INSERT INTO ...rest of statement as shown above...
IF @@ERROR <> 0
BEGIN
...error processing...
ROLLBACK TRANSACTION
GO TO exitCode
END --IF
exitCode:
IF @@TRANCOUNT > 0
COMMIT TRANSACTION

Thirt

ASKER

You sir are a Genius! Let me run through this and give it a shot.

Thirt

ASKER

oh crap, I lied. The captions table actualls holds the values:
fldfieldno      fldcaption
26      11/16/07
27      11/23/07
28      11/30/07
29      12/07/07
30      12/14/07
31      12/21/07
32      12/28/07
33      01/04/08
34      01/11/08
35      01/18/08
36      01/25/08
9      07/20/07
10      07/27/07
11      08/03/07
12      08/10/07
13      08/17/07
14      08/24/07
15      08/31/07
16      09/07/07
17      09/14/07
18      09/21/07
19      09/28/07
20      10/05/07
21      10/12/07
22      10/19/07
23      10/26/07
24      11/02/07
25      11/09/07

What I need to start off where fldfieldno = 11, and then increment up to value 36 until I provide all 26 week date ending values.

Is this workable with your code?

Thirt

ASKER

I went ahead and created a new captions table with records that match my original post. Your SP works perfectly!! I'm still trying to wrap my head around it, as I don't fully understand how it's walking through the week fields. I suspect the Cross Join is a large part, so I will research that.

In the meantime, I can update the caption table before running your your sp. But if it's possible to perform the same insert with the actual caption table I have on hand, that would be better!! Thanks again!