Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Resorting of steps int value part of workflow acti

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-02-27 : 19:11:10
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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-27 : 19:20:20
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
Aged Yak Warrior

567 Posts

Posted - 2013-02-27 : 20:24:34
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
Aged Yak Warrior

567 Posts

Posted - 2013-02-27 : 20:30:57
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)

17689 Posts

Posted - 2013-02-27 : 21:43:53
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -