May 17, 2011 at 12:32 pm
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
May 17, 2011 at 12:43 pm
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
May 17, 2011 at 12:52 pm
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
May 18, 2011 at 2:13 am
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
May 18, 2011 at 7:40 am
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
May 18, 2011 at 10:41 am
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?
May 18, 2011 at 11:22 am
: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
May 18, 2011 at 11:23 am
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