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)
 How to check whether the column values in order
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

532 Posts

Posted - 02/27/2013 :  20:41:25  Show Profile  Reply with Quote
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.

Edited by - cplusplus on 02/27/2013 20:48:16

khtan
In (Som, Ni, Yak)

Singapore
17434 Posts

Posted - 02/27/2013 :  21:37:41  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

cplusplus
Aged Yak Warrior

532 Posts

Posted - 02/27/2013 :  21:51:43  Show Profile  Reply with Quote
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.



Edited by - cplusplus on 02/27/2013 21:53:14
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17434 Posts

Posted - 02/27/2013 :  21:59:36  Show Profile  Reply with Quote
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



KH
Time is always against us

Go to Top of Page

cplusplus
Aged Yak Warrior

532 Posts

Posted - 02/27/2013 :  22:05:04  Show Profile  Reply with Quote
Thank you very much KHTAN, Thank U.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17434 Posts

Posted - 02/27/2013 :  22:06:59  Show Profile  Reply with Quote
welcome


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