Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Insert records loop for dates

    Hi guys,

    I have no idea how to even attempt this.

    I have a table with employee references and a startdate.

    I want to insert into a new table an entry for each employee for each date since their startdate to today.

    Eg

    EMPTABLE

    empref,startdate

    0001,01.01.2012
    0002,02.02.2012

    What I require is

    NEWTABLE

    empref, Date
    0001,01.01.2012
    0001,01.02.2012
    0001,01.03.2012
    ......
    0001,08.27.2012
    0002,02.02.2012
    0002,02.03.2012
    ......
    0002,08.27.2012

    Help appreciated!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    For this you need a "calendar" table, or at least a "numbers" table/function, that you can cross join with employees.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Sep 2011
    Posts
    44
    Thanks,

    So say I have a calendar table from the earliest startdate,

    how then do I loop employees to this calendar.

    eg

    CAL_TABLE

    rec_no, date

    1, 01.01.2012
    2, 01.02.2012
    ....

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    You can also try something like that:

    Code:
    with CTE as
    (
        select empref, startdate from EmpTable
    
        union all
    
        select empref, DATEADD(day, 1, startdate) from CTE
        where startdate < CAST(GETDATE() AS DATE)
    )
    
    insert into NewTable (empref, startdate)
        SELECT empref, startdate FROM CTE 
        ORDER BY empref, startdate
        OPTION (MAXRECURSION 400);
    Hope this helps.
    Last edited by imex; 08-27-12 at 18:16.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by cocono1 View Post
    I want to insert into a new table an entry for each employee for each date since their startdate to today.
    OK....I'll bite. Why do you want to do this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2011
    Posts
    44
    I'm working with a third part software and its required.

    I got it working with a seperate calendar table.

    Thanks!

Posting Permissions

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