Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    37

    Unanswered: set-based update - table joined on itself

    Guys - sorry for the long post - hope it's clear

    DDL/DML below

    I want to update the startdate column (for all rows) so that when period is 0 then the new value
    is a hardcoded value (say '01-Dec-2000') but for all other rows it takes the value in the
    enddate column for the row of the previous column (with the same freq)

    ie the startdate column for period 1 takes the enddate value for period 0 and so on for a particular freq

    create table #periods (period int , startdate datetime , [enddate] datetime , freq int)
    insert #periods ( period , startdate , enddate , freq)
    select 0 , '01-Jan-1900' , '31-Jan-2001' , 1
    union all
    select 1 , '01-Jan-1900' , '28-Feb-2001' , 1
    union all
    select 2 , '01-Jan-1900' , '31-Mar-2001' , 1
    union all
    select 3 , '01-Jan-1900' , '30-Apr-2001' , 1
    union all
    select 4 , '01-Jan-1900' , '31-May-2001' , 1
    union all
    select 0 , '01-Jan-1900' , '31-Jan-2002' , 3
    union all
    select 1 , '01-Jan-1900' , '28-Feb-2002' , 3
    union all
    select 2 , '01-Jan-1900' , '31-Mar-2002' , 3
    union all
    select 3 , '01-Jan-1900' , '30-Apr-2002' , 3
    union all
    select 4 , '01-Jan-1900' , '31-May-2002' , 3

    select * from #periods -- gives

    period start end freq
    0 1900-01-01 00:00:00.000 2001-01-31 00:00:00.000 1
    1 1900-01-01 00:00:00.000 2001-02-28 00:00:00.000 1
    2 1900-01-01 00:00:00.000 2001-03-31 00:00:00.000 1
    3 1900-01-01 00:00:00.000 2001-04-30 00:00:00.000 1
    4 1900-01-01 00:00:00.000 2001-05-31 00:00:00.000 1
    0 1900-01-01 00:00:00.000 2002-01-31 00:00:00.000 3
    1 1900-01-01 00:00:00.000 2002-02-28 00:00:00.000 3
    2 1900-01-01 00:00:00.000 2002-03-31 00:00:00.000 3
    3 1900-01-01 00:00:00.000 2002-04-30 00:00:00.000 3
    4 1900-01-01 00:00:00.000 2002-05-31 00:00:00.000 3



    Desired result
    select * from #periods -- gives

    period start end freq
    0 2000-12-01 00:00:00.000 2001-01-31 00:00:00.000 1
    1 2001-01-31 00:00:00.000 2001-02-28 00:00:00.000 1
    2 2001-02-28 00:00:00.000 2001-03-31 00:00:00.000 1
    3 2001-03-31 00:00:00.000 2001-04-30 00:00:00.000 1
    4 2001-04-30 00:00:00.000 2001-05-31 00:00:00.000 1
    0 2000-12-01 00:00:00.000 2002-01-31 00:00:00.000 3
    1 2002-01-31 00:00:00.000 2002-02-28 00:00:00.000 3
    2 2002-02-28 00:00:00.000 2002-03-31 00:00:00.000 3
    3 2002-03-31 00:00:00.000 2002-04-30 00:00:00.000 3
    4 2002-04-30 00:00:00.000 2002-05-31 00:00:00.000 3


    /*
    I know I need a case statement to test for column 0 and to join the table on itself and have put something together
    but it fails for column 0 and updates to NULL - I think it must be to do with the join ??

    This is what I've got so far :

    UPDATE PA1
    SET
    PA1.Startdate =
    CASE
    WHEN PA2.period = 0
    THEN
    2000-12-01 00:00:00.000
    ELSE
    PA1.Enddate
    END
    FROM #periods AS PA1
    JOIN #periods AS PA2 ON PA1.Freq = PA2.Freq AND PA1.Period = PA2.Period + 1

    Any help gratefully received as always
    */

  2. #2
    Join Date
    Oct 2005
    Posts
    37

    Angry

    Oops - date should have been in quotes

    UPDATE PA1
    SET
    PA1.Startdate =
    CASE
    WHEN PA2.period = 0
    THEN
    '2000-12-01 00:00:00.000'
    ELSE
    PA1.Enddate
    END
    FROM #periods AS PA1
    JOIN #periods AS PA2 ON PA1.Freq = PA2.Freq AND PA1.Period = PA2.Period + 1

  3. #3
    Join Date
    Feb 2004
    Posts
    88
    Hi,

    This works on the table #periods as initially created/populated:


    update p1
    set startdate = p2.enddate
    from #periods p1,
    #periods p2
    where p2.freq = p1.freq
    and p2.period = (select max(p3.period)
    from #periods p3
    where p3.freq = p1.freq
    and p3.period < p1.period)


    HTH,

    Bill

  4. #4
    Join Date
    Oct 2005
    Posts
    37
    Hi Bill
    Thanks but it's not quite right - the problem is in the exception/case statement where the period is 0 which 'doesn't work' - see my modification tio your code to include this

    Hope you can help more

    update p1
    set startdate =

    CASE
    WHEN p1.period = 0
    THEN
    '01-Dec-2000'
    ELSE
    p2.enddate
    END


    from #periods p1,
    #periods p2
    where p2.freq = p1.freq
    and p2.period = (select max(p3.period)
    from #periods p3
    where p3.freq = p1.freq
    and p3.period < p1.period)

  5. #5
    Join Date
    Feb 2004
    Posts
    88
    if yours works, that's cool

    this also does:

    update p1
    set startdate = isnull(p2.enddate, 'Jan 17 1905')
    from #periods p1
    left outer join #periods p2
    on p2.freq = p1.freq
    and p2.period = (select max(p3.period)
    from #periods p3
    where p3.freq = p1.freq
    and p3.period < p1.period)

    - the 'jan 17 1905' thing was just for my benefit....change to your desired date.

  6. #6
    Join Date
    Oct 2005
    Posts
    37
    Hi Bill - Mine didn't work - the case statement I posted was how I expected it to look but as I say it didn't retrieve the result I need but yours is great - thanks again

Posting Permissions

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