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)
 How to check whether the column values in order

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-02-27 : 20:41:25
How to check if the columne STEP values are in order, in the below case there is a break after step 4, it is showing 6. 5 is missing.

In that case variable @StepInOrder = False, multiple activity rows can have same step numbers like step 6.

The order of column step values need to be checked based on "modid" and "modtype".



Declare @StepInOrder as bit

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:
@StepInOrder = false


Thanks a lot for the helpful info.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-02-27 : 21:37:41
quote:
The order of column step values need to be checked based on "modid" and "modtype".

can you explain what do you mean by this ?



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-02-27 : 21:51:43
Hi, what i mean is i want to check if the column step values are in order or not. In the given example 1 thru 7 is there but a number is missing.
which is 5.

The @stepInOrder should show as False.

if 1 thru 7 all number are there without any missing number, then @StepInOrder should be True.

These steps are nothing but workflow items, where a manager can assign activities to different persons. there could be activities with same step, meaning all those activity tasks cab be done parallely.

So we are using step to dictate the order of the activity.
similar to critical path method.

Thank you very much for the helpful info.


Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-02-27 : 21:59:36
[code]select @StepInOrder = 1 -- initialize it
if exists
(
select 1
from @Sample
having count(*) <> count(distinct step)
or count(*) <> max(step) - min(step) + 1
)
select @StepInOrder = 0 -- false

select @StepInOrder[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-02-27 : 22:05:04
Thank you very much KHTAN, Thank U.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-02-27 : 22:06:59
welcome


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -