Thirt
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
You sir are a Genius! Let me run through this and give it a shot.
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?
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?
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!
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!
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...