This is part of workflows.
I have the following table @Sample, now i want to validate Tsk_Complete_date, users can close sequentially by step , In this example step 1 records are completed, because "Task_completed_date" is filled.
meaning the tasks with step 1 are completed the next task which can be completed is with step 2 only.
There could be multiple tasks with same step, since tasks can be parallel or sequential. workflow concept i am using.
Now if the user is trying to close step 3 task, the system should not allow, how to check if there are any tasks between the max closes step and the step 3.
There is one task which is in step2, so the system should not allow.
I am using this in Sp, where i pass whcih step user is trying to close: @Step where in this scenario i pass 3.
Want to check what is teh maxstepalready closed based on modid, if the step which i am planning to close is equal to the max step closed also fine but there shouldnt be any step open, now if i close 3. Step 2 is still open.
The user has to close step2 first before attempting to close step3.
declare @Sample table (ActivityID int, step int, modid int, Tsk_Complete_date date)
select 23, 1,216,'20120712' union all
select 25, 1,216,'20120718' union all
select 34, 2,216,NULL union all
select 38, 3,216,NULL
select * from @Sample
Thanks a lot for the helpful info.