assign the same value to new column till new value changes in the table column

  • Hi,

    I have ClientID, Client rank.

    42A

    42B

    42A

    43A

    44A

    44B

    44A

    44B

    45A

    45B

    45B

    45A

    45B

    I want one more column name cycle to assign the same number to each id till next A rank. That is

    ID Rank Cycle

    42A1

    42B1

    42A2

    43A1

    44A1

    44B1

    44A2

    44B2

    45A1

    45B1

    45B1

    45A2

    45B2

    Any help is appreciated.

    Thanks

  • How would you differentiate between

    ID Rank Cycle

    42 A 1

    42 B 1

    42 A 2

    and

    ID Rank Cycle

    42 A 2

    42 B 1

    42 A 1

    ?

    In other words: there is an additional column missing to uniquely identify a row.

    Once you have that, you could use ROW_NUMBER() OVER(PARTITION BY ID, Rank ORDER BY NewColumn) AS Cycle



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks for replying,

    I have one more column,

    ID rank cycle(to be generated) Ship date

    42A1 October 14, 2010

    42B1 October 26, 2010

    42A2 February 16, 2011

    43A1 December 17, 2010

    44A1 July 28, 2010

    44B1 August 10, 2010

    44A2 January 11, 2011

    44B2 January 28, 2011

    45A1 July 30, 2010

    45B1 August 9, 2010

    45B1 September 24, 2010

    45A2 April 5, 2011

    45B2 April 26, 2011

    May be this works. Cycle column should be genrated that way that number should be same till next 'A' rank for each id.

    Thanks

  • CReate table #tmp(id INT ,client_rank CHAR(1),Cycle INT,shipdate datetime)

    INSERT INTO #tmp(id,client_rank,shipdate)

    values

    (43,'A','December 17, 2010')

    ,(44,'A','July 28, 2010')

    ,(44,'B','August 10, 2010')

    ,(44,'A','January 11, 2011')

    ,(44,'B','January 28, 2011')

    ,(45,'A','July 30, 2010')

    ,(45,'B','August 9, 2010')

    ,(45,'B','September 24, 2010')

    ,(45,'A','April 5, 2011')

    ,(45,'B','April 26, 2011')

    SELECT * , RANK() OVER(partition by id, client_rank ORDER BY SHIPDATE) rn

    FROm #tmp order by id,rn,client_rank

    I hope this will work out

    Thanks

    Vineet Bhargava

    Thanks And Regards
    Vineet Bhargava
    vineetbhargav@gmail.com

  • Hi Vineet,

    I did that way but it do not fulfill the requirement.

    It do not work for ID 45. as it assign value 2 to second 'B' rank.

    cycle should be assigned in aa way that for each C_ID, it generate value start for 1 and assign 2 when second'A' is there.

    Thanks,

    Punia

  • punia (5/18/2011)


    Hi Vineet,

    I did that way but it do not fulfill the requirement.

    It do not work for ID 45. as it assign value 2 to second 'B' rank.

    cycle should be assigned in aa way that for each C_ID, it generate value start for 1 and assign 2 when second'A' is there.

    Thanks,

    Punia

    That's why I recommended to use ROW_NUMBER(). Did you try to adopt the code snippet I provided earlier?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • :Whistling: Actually, you need both the ROW_NUMBER and DENSE_RANK functions...

    ;

    WITH cte AS

    (

    SELECT * ,

    Grp = ROW_NUMBER() OVER (PARTITION BY id ORDER BY shipdate) -

    ROW_NUMBER() OVER (PARTITION BY id, client_rank ORDER BY shipdate)

    FROm #tmp

    )

    SELECT id,

    client_rank,

    Cycle = DENSE_RANK() OVER (PARTITION BY id, client_rank ORDER BY Grp)

    FROM cte

    ORDER BY id, shipdate;

    Edit: Check out this article for how those functions work: SQL Server Ranking Functions[/url]

    :smooooth:

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • yes i tried row_number also partioned by C_ID and C-rank, but it gives same numbers. Not working.

    I tries something like this and it works:

    create table #Temp_Table

    (

    C_ID int

    , C_Rank char(1)

    , Date datetime

    , NewColumn int

    )

    insert into #Temp_Table

    (

    C_ID

    , C_Rank

    , Date

    )

    select 42, ‘A’, ’10/14/2010'

    union all

    select 42, ‘B’, ’10/26/2010'

    union all

    select 42, ‘B’, ’10/14/2010'

    union all

    select 42, ‘C’, ’10/26/2010'

    union all

    select 42, ‘A’,’02/16/2011'

    union all

    select 43, ‘A’, ’12/17/2010'

    union all

    select 44, ‘A’, ’07/28/2010'

    union all

    select 44, ‘B’, ’08/10/2010'

    union all

    select 44, ‘A’, ’01/11/2011'

    union all

    select 44, ‘B’, ’01/28/2011'

    union all

    select 44, ‘C’, ’10/14/2010'

    union all

    select 44, ‘D’, ’10/26/2010'

    Select ‘Original Data’ Comment

    ,*

    from #Temp_Table

    /*

    This would be Actual Script to get the New ID based on information you provided

    */

    Declare @Count int

    ,@C_ID int

    ,@C_Rank char(1)

    ,@total_Count int

    ,@Count_Partition int

    ,@Previous_ID int

    Declare @Table Table (ID int IDENTITY(1,1), C_ID int, C_Rank char(1), Date datetime, NewColumn int )

    Set @Count = 1

    Set @Count_Partition = 0

    insert into @Table

    Select *

    from #Temp_Table

    Select @total_Count = ISNULL(MAX(ID),0)

    from @Table

    While @Count < = @total_Count

    Begin

    Select @C_ID = C_ID

    ,@C_Rank = C_Rank

    From @Table

    Where ID = @Count

    If @Count = 1

    Set @Previous_ID = @C_ID

    If @Previous_ID != @C_ID

    Set @Count_Partition = 1

    Else If @C_Rank = 'A'

    Set @Count_Partition = @Count_Partition + 1

    update @Table

    Set NewColumn = @Count_Partition

    Where ID = @Count

    Set @Previous_ID = @C_ID

    Set @Count = @Count + 1

    End

    Select C_ID

    , C_Rank

    , [Date]

    , NewColumn

    from @Table

    –Drop table #Temp_Table

    Thanks to all for replying

    Punia

Viewing 8 posts - 1 through 8 (of 8 total)

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