Fill in gaps on existing data

Posted on 2012-08-15
Last Modified: 2012-08-15
I have existing data that has of the week and hours of operation. The existing data uses 1-7 for days of week, but I need to subtract 1 to load into my databse (this is an ETL process).

If the entity is not open on a specific day in the source data, it is not present. However, in my table, I need to create a record for each day of the week. I have an IsClosed indicator if the entity is closed on that day.

Thus, I need to load the following source data:
DAY      START      END
1      0800      2200
2      0800      2200
3      0800      2200
4      0800      2200
5      0800      2300
6      0800      2300

into the following destination structure:
Day      Start      End      IsClosed
0      0800      2200      0
1      0800      2200      0
2      0800      2200      0
3      0800      2200      0
4      0800      2300      0
5      0800      2300      0
6      NULL      NULL      1

Note, I've subtracted 1 from the source Day, and identified Day 7 in the source (converted to 6) as missing, so I've 'created' a record with NULL times and IsClosed = 1.

Please try to avoid a recursive solution and we are dealing with millions of records, and I don't want to bring our server to its knees.
Question by:dbbishop
    LVL 5

    Expert Comment

    Guess we miss an entity ID, but I would create a temp table with 7 rows (0-6) holding the day value.
    Now the original table can be JOINed using the "-1" formula for the day field and making the temp table mandatory. (A so-called left or right outer join).

    Now you'll get for each entity 7 rows and for each missing entity the entity ID can be tested for NULL and use that for making the IsClosed 1 or 0.
    LVL 15

    Author Comment

    Would work except (and perhaps I should have shown it in my data, there is also an EntityID that needs to be carried over into the 'missing' data.
    LVL 5

    Accepted Solution

    Then start with building the temp table based on a
    INTO FullTempTable
    from YourBaseTable, TempTable

    The TempTable with the 0-6 will cause a cartesian product, thus making the complete finally needed number of records for your table. Using this FullTempTable LEFT (or RIGHT) JOINed like described earlier by the DAY and ENTITYID should do the trick.
    LVL 15

    Author Closing Comment

    got it. thx.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    754 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

    20 Experts available now in Live!

    Get 1:1 Help Now