Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    19

    Unanswered: Adding Dummy Records By Group

    Hi All,

    I have a scenario where I have to add a certain number of dummy records to each group in a table. My input data looks something like this

    Code:
    Prod ID  Sales ID YrMth
    1           1             201307
    1           1             201306
    1           1             201305
    1            1            201302
    .
    .
    .
    1           1            201207
    1           1            201205
    1           2            201307
    1           2            201305
    .
    .
    1           2           201204
    As can be seen a few YrMnth combinations for the each ProdID-SalesID group are missing. e.g. 201304 and 201303 for Prod ID=1 and Sales ID =1. The requirement here is to add a dummy record wherever there are missing values. So, I'd have to add a record

    Code:
    1 1 201304
    1 1 201303
    There are 37000 such groups and I have to ensure that there are exactly 36 rows(1 for each Yr Month requried) for each group.
    I wrote a PL/SQL block that would go to each group, loop through 1 to 36 and wherever there's a missing month, it would insert a record with the missing time period.

    But this process is taking an eternity. Is there a better way to achieve this?

    Thank You

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    firstly, you have to determine, which "YrMnth combinations" are "missing". That means, list all required ones. Or, is "190001" also required? If not, why?

    In Oracle, this list should be represented by a table. It could be static one or it may be generated from its minimal/maximal values, e.g. as described in this thread on OTN: https://forums.oracle.com/thread/1120618

    Then, the query is a simple OUTER join with the "table" described above.

  3. #3
    Join Date
    Apr 2013
    Posts
    19
    The idea here is to have data for 36 continuous months. We have to pick the max date available for a given group and fill up data up to 36 months before the max date. The months for which data is already available should be ignored(not updated) since theywill hold some valid sales amounts. The dummies inserted will have nulls or 0 sales amounts.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by jerome.r View Post
    The idea here is to have data for 36 continuous months. We have to pick the max date available for a given group and fill up data up to 36 months before the max date. The months for which data is already available should be ignored(not updated) since theywill hold some valid sales amounts. The dummies inserted will have nulls or 0 sales amounts.
    Flyboy's answer still stands as far as I can tell. Do you think that it doesn't? Can you explain why?

  5. #5
    Join Date
    Apr 2013
    Posts
    19
    Thank you! I capitalized on the line of thought you guys gave and it helped. Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •