Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    29

    Unanswered: Sequence number for a group

    Hi Experts,

    Can you please suggest me for the following scenario,

    I have a main table with two columns id and date. I also have a history table with three columns seq_num,id and date.

    Main table:

    id date
    1 Apr 5
    1 Apr 6
    2 Apr 6
    3 Apr 7
    2 Apr 7
    1 Apr 8

    When I insert the above rows into the history table, I want the sequence number for each group(based on the id). The history table should look like,

    seq_num id date
    1 1 Apr 5
    2 1 Apr 6
    3 1 Apr 8
    1 2 Apr 6
    2 2 Apr 7
    1 3 Apr 7

    So I want the sequence number to be populated for the each id group. Hope I am clear with my requirement.

    Kindly suggest me a way to do the same.

    Thanks in Advance,
    Chella

  2. #2
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68

    If u can use stored procedure and performance is not a issue then...

    Create procedure seq_idgen
    As
    Begin
    declare @temp_id int
    declate @hist_cnt int

    select @hist_cnt = count(*) from history
    if @hist_cnt > 0
    truncate table history

    declare idgen_crsr cursor
    for select distinct id
    from main_table order by id
    for read only

    create table #temp(seq_no int, id int, date varchar(20))

    open idgen_crsr

    fetch idgen_crsr into @temp_id

    while (@@sqlstatus = 0)
    begin

    create table #temp1(seq_no int identity, id int, date varchar(20))

    select * into #temp1 from main_table where id = @temp_id

    insert #temp
    select * from #temp1

    drop table #temp1

    fetch idgen_crsr into @temp_id
    End

    close idgen_crsr

    deallocate cursor idgen_crsr

    insert history
    select * from #temp group by seq_no order by seq_no, id

    drop table #temp
    End
    Last edited by parangiri; 04-09-08 at 00:14.

  3. #3
    Join Date
    Mar 2007
    Posts
    29
    Thanks a lot for the reply.

    Can you tell me if there is a way to achieve the same scenario without using cursors (looping).

    Kindly suggest.


    Regards,
    Chella

  4. #4
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Changes in bold are done as a replacement to cursor

    Create procedure seq_idgen
    As
    Begin
    declare @temp_id int
    declare @hist_cnt int
    declare @id_main int
    declare @cnt int

    select @hist_cnt = count(*) from history
    if @hist_cnt > 0
    truncate table history

    create table #temp_iden(id int)

    insert #temp_iden
    select distinct id from main_table order by id

    create table #temp(seq_no int, id int, date varchar(20))

    set rowcount 1
    select @id_main = id from #temp_iden
    set rowcount 0
    select @cnt = count(id) from #temp_iden

    while (@cnt > 0)
    begin

    create table #temp1(seq_no int identity, id int, date varchar(20))

    select * into #temp1 from main_table where id = @id_main

    insert #temp
    select * from #temp1

    drop table #temp1

    delete #temp_iden where id = @id_main
    set rowcount 1
    select @id_main = id from #temp_iden
    set rowcount 0
    select @cnt = count(id) from #temp_iden

    End

    insert history
    select * from #temp group by seq_no order by seq_no, id

    drop table #temp
    drop table #temp_iden
    End
    Last edited by parangiri; 04-10-08 at 00:18.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    instead of while loops
    Code:
    --create the main test data--
    create table #t1 (id int, dt datetime)
    insert into #t1 select /*
    id date */
    1, 'Apr 5 2008' union all select
    1, 'Apr 6 2008' union all select
    2, 'Apr 6 2008' union all select
    3, 'Apr 7 2008' union all select
    2, 'Apr 7 2008' union all select
    1, 'Apr 8 2008' 
    -- done create test data --
    
    select sq = identity(9), id, dt into #t2 from #t1 order by id,dt
    
    --insert into yourhist
    select sq-mn+1 sq, a.id, dt from #t2 a
    join (select id, min(sq) mn from #t2 group by id)b
    on a.id = b.id
    order by id,sq
    
    drop table #t1
    drop table #t2

Posting Permissions

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