Store Procedure to build norm table from Denorm table

Posted on 2007-07-26
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!

Question by:Thirt
    LVL 35

    Expert Comment

    >>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


    Anyway, you can do it by something like

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

    LVL 68

    Accepted Solution

    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
    LVL 68

    Expert Comment

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

    TRUNCATE TABLE normalized
    IF @@ERROR <> 0
        ...error processing...
        GO TO exitCode
    END --IF
    INSERT INTO of statement as shown above...
    IF @@ERROR <> 0
        ...error processing...
        GO TO exitCode
    END --IF
    IF @@TRANCOUNT > 0
    LVL 4

    Author Comment

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

    Author Comment

    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?
    LVL 4

    Author Comment

    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!

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now