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

    Question Unanswered: how to write set-based SQL instead of cursor

    Guys
    Here's the scenario

    create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT)
    insert data1 (dealid , datex , Tn )
    values ('12345' , '31-12-2005' , 9999)
    insert data1 (dealid , datex , Tn )
    values ('12345' , '30-11-2005' , 9999)
    insert data1 (dealid , datex , Tn )
    values ('12345' , '31-10-2005' , 9999)
    insert data1 (dealid , datex , Tn )
    values ('98765' , '31-12-2005' , 2)
    insert data1 (dealid , datex , Tn )
    values ('98765' , '30-11-2005' , 1)
    insert data1 (dealid , datex , Tn )
    values ('98765' , '30-11-2005' , 0)
    select * from data1


    I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way
    Any ideas

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    set nocount on
    create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT)
    insert data1 (dealid , datex , Tn )
    values ('12345' , '2005-12-31' , 9999)
    insert data1 (dealid , datex , Tn )
    values ('12345' , '2005-11-30' , 9999)
    insert data1 (dealid , datex , Tn )
    values ('12345' , '2005-10-31' , 9999)
    insert data1 (dealid , datex , Tn )
    values ('98765' , '2005-12-31' , 2)
    insert data1 (dealid , datex , Tn )
    values ('98765' , '2005-11-30' , 1)
    insert data1 (dealid , datex , Tn )
    values ('98765' , '2005-11-30' , 0)
    
    select * from data1 order by dealid, datex, Tn
    
    update	data1
    set	Tn = (select count(*) from data1 data1_2 where data1.dealid = data1_2.dealid and data1.datex >= data1_2.datex)
    from	data1
    where	data1.Tn = 9999
    
    select * from data1 order by dealid, datex, Tn
    
    drop table data1
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Very nice....I was getting caught up in MAX(datex)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2005
    Posts
    37
    Thx - exactly what I needed

Posting Permissions

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