SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Resorting of steps int value part of workflow acti		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Constraint Violating Yak Guru

481 Posts
Posted - 02/27/2013 :  19:11:10  Show Profile  Reply with Quote
Is it possible to Resort the steps column int value by update queries based on activityId(unique column), which is part of workflow activities, these steps order are read identified based on modid and modtype: ActivityID is unique primary key column:


Declare @Sample table (ActivityID int, step int, ModId int, ModType Varchar(2))
insert @Sample
select 125, 1, 143, 'BP' union all
select 144, 2, 143, 'BP' union all
select 152, 3, 143, 'BP' union all
select 161, 4, 143, 'BP' union all
select 177, 6, 143, 'BP' union all
select 181, 6, 143, 'BP' union all
select 191, 7, 143, 'BP'



Result should be these 7 activity rows sorting the step int value for all activities:there could be multiple steps with same step number like two activites above with step 6 should become 5. resorting of numbering of steps using update query. 


125	1 	143	'BP'	

144	2 	143	'BP'	

152	3 	143	'BP'	

161	4 	143	'BP'	

177	5 	143	'BP'

181	5 	143	'BP'

191	6 	143	'BP'


Thank you very much for the helpful info.
Edited by - cplusplus on 02/27/2013 19:14:11

James K
Flowing Fount of Yak Knowledge

1481 Posts
Posted - 02/27/2013 :  19:20:20  Show Profile  Reply with Quote
I didn't quite follow your description, so the code below is based on the sample output data you posted. See if it works for you?
SELECT
	*,
	DENSE_RANK() OVER (ORDER BY step) AS NewStep
FROM
	@Sample
Go to Top of Page

cplusplus
Constraint Violating Yak Guru

481 Posts
Posted - 02/27/2013 :  20:24:34  Show Profile  Reply with Quote
James, Thanks it worked with Newstep, resorting of the step numbers.

But how can i use it within Update query, updating existing rows based on activity ID.

I have almost 200,000 rows within this table with different modid/modtype combinations.

Thanks a lot for the helpful info.
Go to Top of Page

cplusplus
Constraint Violating Yak Guru

481 Posts
Posted - 02/27/2013 :  20:30:57  Show Profile  Reply with Quote
Can i load this new resorted data into a cursor and and the loop it one by one with update query?

I hope this will work.

Thank you very much for the help.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16745 Posts
Posted - 02/27/2013 :  21:43:53  Show Profile  Reply with Quote
quote:
Originally posted by cplusplus

Can i load this new resorted data into a cursor and and the loop it one by one with update query?

I hope this will work.

Thank you very much for the help.



You can but you should not be using a cursor to do it.

You can do it in a simple query.

UPDATE 	s
SET	step	= NewStep
FROM	
(
	SELECT
		*,
		DENSE_RANK() OVER (ORDER BY step) AS NewStep
	FROM
		@Sample
) s
WHERE   step	<> NewStep


KH
Time is always against us
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000