populate table column
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: populate table column

  1. #1
    Join Date
    Jul 2001
    Posts
    334

    populate table column

    Hi All,

    I have a simple table with three columns as below:
    person_id period_start period_end

    I would like to populate this temp table with the following WEEKLY data for each employee.
    person_id period_start period_end
    ======= ========= ========
    100 01-JAN-2007 07-JAN-2007
    101 01-JAN-2007 07-JAN-2007
    102 01-JAN-2007 07-JAN-2007
    100 08-JAN-2007 14-JAN-2007
    101 08-JAN-2007 14-JAN-2007
    102 08-JAN-2007 14-JAN-2007
    .
    .
    .
    100 24-DEC-2007 30-DEC-2007
    101 24-DEC-2007 30-DEC-2007
    102 24-DEC-2007 30-DEC-2007

    Note: The period_start should start with 01-JAN-2007 and end 30-DEC-2007 for each employee.

    Thanks in advance.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool


    Use the MIN() and MAX() functions.


    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by LKBrwn_DBA

    Use the MIN() and MAX()
    mmhhh... may I politely ask you ... to do what?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool


    Something like this:
    Code:
    CREATE TempTable
    As
    SELECT person_id
         , MIN(period_start) period_start
         , MAX(period_end) period_end
      FROM WeeklyData
     GROUP BY person_id;

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I see... nice solution, congrats!

    Now, wondering if you have another one that actually solves what's in the specs

    LOL
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    It would be easier if I actually knew what you wanted grouped by week.

    But here is my take on this.

    Code:
    SELECT person_id, week period_start, week+7 period_end
      FROM ( SELECT person_id, TRUNC(period_start, 'W') week
               FROM weekly_data
              WHERE TO_CHAR(period_start, 'YYYY') = '2007'
              GROUP BY person_id, TRUNC(period_start, 'W') );
    this space intentionally left blank

  7. #7
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool

    Quote Originally Posted by PAVB
    I see... nice solution, congrats!

    Now, wondering if you have another one that actually solves what's in the specs

    LOL

    Ooops, now I see that what I assumed was the source data is actually the result table. DUH!

    Better try something like this:
    Code:
    Select Person_Id, Week Period_Start, Week+7 Period_End
      From ( Select Employee_Id Person_Id From Employees Where Rownum <4) P
         , ( Select To_Date('20070101','Yyyymmdd') + (Level-1)*7 Week
               From Dual Connect By Level < 53) W
      Order By 1,2

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

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


Click Here to Expand Forum to Full Width