?
Solved

Store Procedure to build norm table from Denorm table

Posted on 2007-07-26
6
Medium Priority
?
362 Views
Last Modified: 2012-08-14
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!

0
Comment
Question by:Thirt
  • 3
  • 2
6 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 19576058
>>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...

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 19577610
INSERT INTO normalized (ProjectID, EmployeeID, Position, Checked, Probability,
    Week_ending_date, Hours)
SELECT d.ProjectID, d.EmployeeID, d.Position, d.Checked, d.Probability,
    c.fldcaption AS Week_ending_date,
    CASE c.fldFieldno
        WHEN 'fldwk01' THEN d.fldwk01
        WHEN 'fldwk02' THEN d.fldwk02
        WHEN 'fldwk03' THEN d.fldwk03
        WHEN 'fldwk04' THEN d.fldwk04
        --...
        WHEN 'fldwk26' THEN d.fldwk26 END AS Hours
FROM denormalized d
CROSS JOIN lkpcaptions c
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19577649
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:Thirt
ID: 19578652
You sir are a Genius! Let me run through this and give it a shot.
0
 
LVL 4

Author Comment

by:Thirt
ID: 19578687
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?
0
 
LVL 4

Author Comment

by:Thirt
ID: 19594019
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!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question