Sequential Update

  • Sorry, I've been here before, deja-vu all over again.

    Cannot find previous thread.

    I need to update a date field based on the next record in a grouped sequence. The eff_date_end = (eff_date_start -1) from th next record in sequence if it exists. If not,set to 1/1/2099.

    Based on cd1, cd2

    id | cd1 | cd2 | date_start | date_end

    1 | a | a | 1/1/1960 |

    2 | a | a | 4/1/1967 |

    3 | a | a | 8/1/1999 |

    4 | b | b | 1/1/1960 |

    5 | c | c | 1/1/1960 |

    6 | c | c | 7/1/2001 |

    should end up -

    id | cd1 | cd2 | date_start | date_end

    1 | a | a | 1/1/1960 | 3/31/1960

    2 | a | a | 4/1/1967 | 7/31/1967

    3 | a | a | 8/1/1999 | 1/1/2099

    4 | b | b | 1/1/1960 | 1/1/2099

    5 | c | c | 1/1/1960 | 6/30/2001

    6 | c | c | 7/1/2001 | 1/1/2099

    thanks for your support.

    Randy McKnight

  • after-thought;

    just update all eff_end_date with 1/1/2009 prior. Then I don't have to worry about that in the logic.

    Randy.

  • Here's an example - adapt it to using your table and field names.

    use testing

    go

    drop index endmatric.ix_em_coend

    --create the index that will help with the sequencing.

    create index ix_em_coend on endmatric(company,date_start desc)

    --grouping will be determined based on company, and put the start dates in reverse order for traversing the table

    declare @dummy datetime

    declare @def_end datetime

    declare @temp datetime

    declare @previd varchar(2)

    select @previd=''

    set @def_end='1/1/2099'

    set @temp=@def_end

    --the WITH (INDEX()) syntax forces the sort during the update, so it makes the process work

    --the extra throwaway variable @dummy makes the running part work in 2005

    update endmatric

    set

    @dummy=date_end=case when @previd=company then dateadd(dd,-1,@temp) else @def_end end,

    @temp=date_start,

    @previd=company

    from endmatric WITH (index(ix_em_coend),TABLOCK)

    --see what you've done

    select * from endmatric with (index(ix_em_coend),tablock)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thats genius.

    Thanks.

    Randy.

  • I was working on a simialr solution but when I tried your index trick to get the update in the right order it still goes asc. Might be because I am using SQL7

    create index ix_mytable on mytable(cd1,cd2,date_start desc)

    declare @dummy datetime,@newdate datetime, @cd1 varchar(5),@cd2 varchar(5)

    update mytable

    set

    @dummy=date_end = case when @cd1 = cd1 and @cd2 = cd2 then dateadd(dd,-1,@newdate) else '1/1/2009' end,

    @cd1 = cd1,

    @cd2 = cd2,

    @newdate = date_start

    from mytable with (index(ix_mytable),tablock)

    idcd1cd2date_startdate_end

    4aa1960-01-01 00:00:00.0002009-01-01 00:00:00.000

    5aa1967-04-01 00:00:00.0001959-12-31 00:00:00.000

    2aa1999-08-01 00:00:00.0001967-03-31 00:00:00.000

    3bb1960-01-01 00:00:00.0002009-01-01 00:00:00.000

    6cc1960-01-01 00:00:00.0002009-01-01 00:00:00.000

    1cc2001-07-01 00:00:00.0001959-12-31 00:00:00.000

    (6 row(s) affected)


  • Sorry - have no idea if it will work against 7.0.

    If anything I have a gut feeling that the DESC syntax is invalid on 7.0.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • It's probably because the WITH INDEX trick doesn't really work.

    Before anyone blows a fuse, by 'not really work' I mean 'not supported', 'isn't reliable', 'cannot be guranteed 100% to always behave like one thinks', etc....

    This is one of those 'ordering tricks' that indeed seems to work, but it doesn't in the sense of 'can I trust it to always do what I want'.

    It's an exploit on the the physical implementation of the table, and as such SQL Server itself leaves no guarantees that the 'order we expect' will always be preserved in all cases. The key component here is what the execution plan looks like when the query is executed, and I belive, also which isolation level.

    So, what I'm saying is: test it and test it again, and if you really decide to use it, then one is on one's own. That is, use the 'trick' on your own risk.

    /Kenneth

  • MrPoleCat,

    Would you be kind enought to test this on your SQL Server 7? Thank you, Sir...

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#yourtable','U') IS NOT NULL

    DROP TABLE #yourtable

    --===== Recreate the test table

    CREATE TABLE #yourtable

    (

    ID INTEGER PRIMARY KEY CLUSTERED,

    CD1 VARCHAR(10),

    CD2 VARCHAR(10),

    Date_Start DATETIME,

    Date_End DATETIME

    )

    --===== Populate the test table with data from the post

    INSERT INTO #yourtable

    (ID,CD1,CD2,Date_Start)

    SELECT '1','a','a','1/1/1960' UNION ALL

    SELECT '2','a','a','4/1/1967' UNION ALL

    SELECT '3','a','a','8/1/1999' UNION ALL

    SELECT '4','b','b','1/1/1960' UNION ALL

    SELECT '5','c','c','1/1/1960' UNION ALL

    SELECT '6','c','c','7/1/2001'

    --===== Add the required indexes and keys

    CREATE INDEX IDX_yourtable_CD1_CD2_Date_Start

    ON #yourtable (CD1 ASC, CD2 ASC, Date_Start DESC)

    GO

    --===== Declare some require local variables

    DECLARE @PrevCD1 VARCHAR(10)

    DECLARE @PrevCD2 VARCHAR(10)

    DECLARE @PrevStartDate DATETIME

    DECLARE @Dummy DATETIME

    SELECT @PrevCD1 = '', @PrevCD2=''

    --===== Do the update using the index to control the order

    UPDATE #yourtable

    SET @Dummy = Date_End = CASE

    WHEN CD1 = @PrevCD1

    AND CD2 = @PrevCD2

    THEN @PrevStartDate - 1

    ELSE '01/01/2099'

    END,

    @PrevCD1 = CD1,

    @PrevCD2 = CD2,

    @PrevStartDate = Date_Start

    FROM #yourtable WITH (INDEX(IDX_yourtable_CD1_CD2_Date_Start),TABLOCK)

    --===== Display the results in the correct order

    SELECT *

    FROM #yourtable

    ORDER BY CD1,CD2,Date_Start

    --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)

  • have to wait until Monday.


  • Roger that...

    --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)

  • Jeff--

    It throws errors

    Server: Msg 156, Level 15, State 1, Line 27

    Incorrect syntax near the keyword 'ASC'.

    Server: Msg 208, Level 16, State 1, Line 12

    Invalid object name '#yourtable'.


  • mrpolecat (10/29/2007)


    Jeff--

    It throws errors

    Server: Msg 156, Level 15, State 1, Line 27

    Incorrect syntax near the keyword 'ASC'.

    Server: Msg 208, Level 16, State 1, Line 12

    Invalid object name '#yourtable'.

    It's gotta be the way you're pasting the code... I get no errors...

    [font="Courier New"](6 row(s) affected)

    (6 row(s) affected)

    ID CD1 CD2 Date_Start Date_End

    ----------- ---------- ---------- ------------------------------------------------------ ------------------------------------------------------

    1 a a 1960-01-01 00:00:00.000 1967-03-31 00:00:00.000

    2 a a 1967-04-01 00:00:00.000 1999-07-31 00:00:00.000

    3 a a 1999-08-01 00:00:00.000 2099-01-01 00:00:00.000

    4 b b 1960-01-01 00:00:00.000 2099-01-01 00:00:00.000

    5 c c 1960-01-01 00:00:00.000 2001-06-30 00:00:00.000

    6 c c 2001-07-01 00:00:00.000 2099-01-01 00:00:00.000

    (6 row(s) affected)[/font]

    Copy from the code box on the thread, paste into MS Word, replace all ^l (circumflex lower case "L") to ^p (circumflex lower case "P"). Then, copy from that...

    Man, I sure do wish they'd fix this stuff...

    --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)

  • I did that. I fixed the last error which appeared to be a wierd character carried over. The first error "Near ASC" is real. I took out ASC in both spots and it works. I tried replacing them with DESC and it failed. It doesn't throw the error on the last order but it doesn't use it either. Appears to be an SQL 7 non feature.


Viewing 13 posts - 1 through 12 (of 12 total)

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