Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Update end_date from following start_date

    It's another one of those mornings - I just can't wrap myhead round this one and I'm convinced I've done this before (but the search has found me nothing so far)!

    Basically I have a list of positions with start dates. I need to work out the end date for a position; which is equal to the start date of the following record.

    Here's some code to play with.
    Code:
    /*
    Aim: Fill in the end date for a position.
         The end date is the start_date of the following record by date.
    */
    
    DECLARE @position table (
       staff_number nvarchar(7) NOT NULL
     , start_date   datetime    NOT NULL
     , end_date     datetime
    )
    
    INSERT INTO @position (staff_number, start_date)
          SELECT '1', '2006-06-19 00:00:00.000'
    UNION SELECT '1', '2007-06-18 00:00:00.000'
    UNION SELECT '1', '2007-11-01 00:00:00.000'
    UNION SELECT '2', '2006-11-01 00:00:00.000'
    UNION SELECT '2', '2007-06-18 00:00:00.000'
    UNION SELECT '3', '2007-11-19 00:00:00.000'
    UNION SELECT '4', '2007-04-23 00:00:00.000'
    UNION SELECT '4', '2007-10-01 00:00:00.000'
    
    UPDATE first1
    SET    end_date = next1.start_date
    FROM   @position first1
     INNER
      JOIN @position next1
        ON first1.staff_number = next1.staff_number
       AND first1.start_date < next1.start_date
    
    SELECT staff_number
         , start_date
         , end_date
    FROM   @position
    ORDER
        BY staff_number
         , start_date
    
    /* Desired results
    staff_number start_date                end_date                                             
    ------------ ------------------------- -------------------------
    1            2006-06-19 00:00:00.000   2007-06-18 00:00:00.000
    1            2007-06-18 00:00:00.000   2007-11-01 00:00:00.000
    1            2007-11-01 00:00:00.000   NULL
    2            2006-11-01 00:00:00.000   2007-06-18 00:00:00.000
    2            2007-06-18 00:00:00.000   NULL
    3            2007-11-19 00:00:00.000   NULL
    4            2007-04-23 00:00:00.000   2007-10-01 00:00:00.000
    4            2007-10-01 00:00:00.000   NULL
    */
    As always, if I've missed ou any detail needed to solve this problem just let me know.

    Cheers,
    George
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, I feel silly
    Code:
    UPDATE first1
    SET    end_date = next1.start_date
    FROM   @position first1
     LEFT
      JOIN @position next1
        ON first1.staff_number = next1.staff_number
       AND first1.start_date < next1.start_date
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hmm, this appears to rely on the data being in date order... I think..?
    So when running it on the live data it doesn't appear to work!
    Code:
    DECLARE @position table (
       staff_number nvarchar(7) NOT NULL
     , somefield    int identity(1,1) primary key
     , start_date   datetime    NOT NULL
     , end_date     datetime
    )
    
    INSERT INTO @position (staff_number, start_date)
    SELECT '5', '2007-10-01 00:00:00.000'
    INSERT INTO @position (staff_number, start_date)
    SELECT '1', '2005-06-01 00:00:00.000'
    INSERT INTO @position (staff_number, start_date)
    SELECT '1', '1999-01-04 00:00:00.000'
    INSERT INTO @position (staff_number, start_date)
    SELECT '1', '2000-01-04 00:00:00.000'
    INSERT INTO @position (staff_number, start_date)
    SELECT '1', '2003-06-01 00:00:00.000'
    INSERT INTO @position (staff_number, start_date)
    SELECT '2', '2006-06-19 00:00:00.000'
    INSERT INTO @position (staff_number, start_date)
    SELECT '3', '2006-11-01 00:00:00.000'
    INSERT INTO @position (staff_number, start_date)
    SELECT '3', '2007-06-18 00:00:00.000'
    INSERT INTO @position (staff_number, start_date)
    SELECT '4', '2007-11-19 00:00:00.000'
    INSERT INTO @position (staff_number, start_date)
    SELECT '5', '2007-04-23 00:00:00.000'
    INSERT INTO @position (staff_number, start_date)
    SELECT '2', '2007-11-01 00:00:00.000'
    INSERT INTO @position (staff_number, start_date)
    SELECT '2', '2007-06-18 00:00:00.000'
    
    UPDATE first1
    SET    end_date = next1.start_date
    FROM   @position first1
     LEFT
      JOIN @position next1
        ON first1.staff_number = next1.staff_number
       AND first1.start_date < next1.start_date
    
    SELECT staff_number
         , start_date
         , end_date
    FROM   @position
    ORDER
        BY staff_number
         , start_date ASC
    
    /* Desired output
    staff_number start_date                end_date
    ------------ ------------------------- -------------------------
    1            1999-01-04 00:00:00.000   2000-01-04 00:00:00.000
    1            2000-01-04 00:00:00.000   2003-06-01 00:00:00.000
    1            2003-06-01 00:00:00.000   2005-06-01 00:00:00.000
    1            2005-06-01 00:00:00.000   NULL
    2            2006-06-19 00:00:00.000   2007-06-18 00:00:00.000
    2            2007-06-18 00:00:00.000   2007-11-01 00:00:00.000
    2            2007-11-01 00:00:00.000   NULL
    3            2006-11-01 00:00:00.000   2007-06-18 00:00:00.000
    3            2007-06-18 00:00:00.000   NULL
    4            2007-11-19 00:00:00.000   NULL
    5            2007-04-23 00:00:00.000   2007-10-01 00:00:00.000
    5            2007-10-01 00:00:00.000   NULL
    */
    Any ideas?
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    For starters you are using American English on your server.
    http://www.karaszi.com/SQLServer/info_datetime.asp

    Me, the Queen and the rest of the British public are ashamed of you young man.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Here's a question - why are you storing this and not deriving at run time? how are you going to handle changes to a record's start date?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    1999-01-04t00:00:00.000
    ??
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It could be derived at run-time, potentially.
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT      p.staff_number 
        , p.somefield
        , p.start_date
        , end_dates.end_date
    FROM    @position AS p
    CROSS APPLY 
        (
            SELECT    end_date        = MIN(start_date)
            FROM    @position AS der_p
            WHERE    der_p.staff_number    = p.staff_number
                AND der_p.start_date    > p.start_date
        ) AS end_dates
    ORDER BY p.staff_number
        , p.start_date

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Code:
    1999-01-04t00:00:00.000
    ??
    No.
    Code:
    19990104 00:00:00.000
    Seriously - change your connection property to British English and your code fails. Don't put dashes in dates. YYYYMMDD not YYYY-MM-DD. I used to do what you do and had my code crash out when using a British English connection.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    +-------------+-----------------------+----------------+--------------+----------+
    | Name        | Format                | SET DATEFORMAT | SET LANGUAGE | Language |
    |             |                       | dependent      | dependent    | neutral  |
    +-------------+-----------------------+----------------+--------------+----------+
    | Unseparated | '19980223 14:23:05'   | no             | no           | yes      |
    |             |                       |                |              |          |
    | ISO 8601    | '1998-02-23T14:23:05' | no             | no           | yes      |
    +-------------+-----------------------+----------------+--------------+----------+
    Thanks for the suggestion - I'll give it a try
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're going to hate me... compatability level 80
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - I didn't know the "T" could fix it too.

    I do hate you - why the hell have you upgraded from 65 to only 80? If you are upgrading why not 90?

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Because you can't! 80 is the highest you can bump a 6.5 to.
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What stops you bumping an 80 to 90?

    Code:
    SELECT      p.staff_number 
        , p.somefield
        , p.start_date
        , new_end_date        = MIN(gimme_a_p_bob.start_date)
    FROM    @position AS p
    LEFT OUTER JOIN
            @position AS gimme_a_p_bob
    ON    gimme_a_p_bob.staff_number    = p.staff_number
        AND gimme_a_p_bob.start_date  > p.start_date
    GROUP BY p.staff_number 
        , p.somefield
        , p.start_date
    ORDER BY p.staff_number
        , p.start_date
    BTW - the order by is just to match your required output display - it is not required for the query.

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Perfect, thank you poots.

    I tried using Min() before, but I was mind-set on doing it via an update statement

    Writing queries on 3 hours sleep is not very productive
    George
    Home | Blog

Posting Permissions

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