Post reply

Building a heirarchical data structure

  • January 17, 2010 at 8:30 pm

    #209147

    Hello to all experts

    I don't know whether the following can be done or not in T-SQL without the use of front-end programming language

    I have a table which has the following rows:

    Emp_ID Position_ID Start_Date

    1 0200 01-01-2009

    1 0300 01-04-2009

    1 0400 01-08-2009

    2 0200 01-01-2009

    The above rows are in a staging tables. I am building my base tables data out of the staging tables

    I want the following output.

    Emp_ID Position_ID Start_Date End_Date

    1 0200 01-01-2009 31-03-2009

    1 0300 01-04-2009 31-07-2009

    1 0400 01-08-2009 NULL

    2 0200 01-01-2009 NULL

    This means that the End_Date should be a day less than the next position starts for an employee ID.

    Could anyone please throw some light on this.

    Thank you.

  • January 17, 2010 at 9:40 pm

    #1104782

    A self join with a ROW_NUMBER reference would probably do it. If you want a coded answer, please see the first link in my signature line below.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply