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 with query if there are any rows open		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Constraint Violating Yak Guru

483 Posts
Posted - 08/02/2012 :  14:56:08  Show Profile  Reply with Quote
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.
Edited by - cplusplus on 08/02/2012 15:03:15

visakh16
Very Important crosS Applying yaK Herder

India
48091 Posts
Posted - 08/02/2012 :  15:03:02  Show Profile  Reply with Quote
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/
Edited by - visakh16 on 08/02/2012 15:17:22
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.03 seconds. Powered By: Snitz Forums 2000