How to check with query if there are any rows open

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 with query if there are any rows open

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-08-02 : 14:56:08

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)
insert @Sample
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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 15:03:02

use a udf based check constraint on table

like


CREATE FUNCTION CheckOpenSteps
(
@step int,
@modid int
)
RETURNS int
AS
BEGIN
DECLARE @Cnt int

SELECT @Cnt = COUNT(*)
FROM Table
WHERE modid=@modid
AND Tsk_Complete_date IS NULL
AND step < @stepid

RETURN (@Cnt)
END


then use it like

ALTER TABLE tablename ADD CONSTRAINT CHk_OpenSteps CHECK(Tsk_Complete_date IS NULL OR dbo.CheckOpenSteps(step,modid) = 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources