Condition Checking Problem - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Condition Checking Problem

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-11-28 : 23:38:27

IF EXISTS (SELECT 1 FROM TProject WHERE (((@From_DT < Estimated_Start_DT OR @From_DT> Estimated_End_DT) OR
(@From_DT < Actual_Start_DT OR @From_DT>Actual_End_DT)) OR

(@End_DT < Estimated_Start_DT OR @End_DT> Estimated_End_DT) OR
(@End_DT < Actual_Start_DT OR @End_DT>Actual_End_DT))
AND Project_NBR=@Project_NBR AND Active_FG=0)

BEGIN

SELECT 'Employee(s) assignment Start Date and End Date should be within the range of the project actual dates. '
RETURN
END

Hi i have this Condition Checking in one of Stored Procedures to check whether an Employee From Date of Project
Falls within the range of Project Duration( Either Estimated or Actual).

This condition is been checked with tProject table

Tproject table has following columns

Project_NBR Project_NM Estimated_Start_DT Estimated_End_DT Actual_Start_DT Actual_End_DT Active_Fg
1 Katmandu 2008-11-05 00:00:00.000 2008-12-10 00:00:00.000 2009-06-06 00:00:00.000 NULL 0

I need to print the message if the From Date of Employee Does not fall between Estimated_Start_Dt - Estimated_End_DT OR Actual_Start_DT Or Actual_End_DT
If it falls in any of this ranges it should not print the message

but this condition fails when i pass the from date as 2008-11-28 and Project number as 1 coz it fails when checked with actual_start_dt - Actual_End_DT.
Can anyone suggest me a better solution for this

Thanks Before Hand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 00:22:05

are you looking at this?


IF EXISTS (SELECT 1 FROM TProject WHERE 
((
(@From_DT < Estimated_Start_DT AND @End_DT> Estimated_Start_DT) OR 
(@From_DT < Actual_Start_DT AND @End_DT>Actual_Start_DT) OR 

(@From_DT < Estimated_End_DT AND @End_DT> Estimated_End_DT) OR 
(@From_DT < Actual_End_DT AND @End_DT>Actual_End_DT))
AND Project_NBR=@Project_NBR AND Active_FG=0)

BEGIN

RAISERROR 'Employee(s) assignment Start Date and End Date should be within the range of the project actual dates. ',10,1

END

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-11-29 : 00:37:28

Yes vishakh Exactly..Thanks for the solution.. So i went wrong in checking the condition properly..

But when i use the raise errror statement which u gave... it gives me the follwing error -

Incorrect syntax near 'Employee(s) assignment Start Date and End Date should be within the range of the project actual dates. '.

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-11-29 : 00:38:48

oh i got it.. the statement after raise error should be kept in braces.. Thanks Anyways

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 00:49:29

quote:
Originally posted by swathigardas

oh i got it.. the statement after raise error should be kept in braces.. Thanks Anyways

i gave that solution as i thought you needed to raise error anyways. if not just use select as you used earlier.

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-19 : 04:28:03

Hi.. I have the problem again in the same query, as the requirement is slightly changed..If the Actual_End_DT is null , then Estimated_End_DT value should be taken for comparision and if Actual_Start_DT is null, then Estimated_End_DT should be taken for comparision.
In TProject table , the data is as follows

Project_NBR Project_NM Estimated_Start_DT Estimated_End_DT Actual_Start_DT Actual_End_DT

31 VProjectsNew1 2008-12-04 00:00:00.000 2009-01-30 00:00:00.000 2008-12-04 00:00:00.000 2009-01-28 00:00:00.000

I'm using this query for the new requirement
For From_DT Variable i kept the value '2008-12-04'
For End_DT - '2009-12-19'

SELECT 1 FROM TProject WHERE (('2008-12-04' < ISNULL(Actual_Start_DT,Estimated_Start_DT) OR '2008-12-04' >ISNULL( Actual_End_DT, Estimated_End_DT)) OR
('2009-12-19' < ISNULL(Actual_Start_DT,Estimated_Start_DT) OR '2009-12-19' > ISNULL( Actual_End_DT, Estimated_End_DT))
AND Project_NBR=31 AND Active_FG=0)

This Above Query Works Fine when i pass the values as above and the return value is 1

But when i change the End Date as '2008-12-19' I still get the value as 1 , where i am not supposed to retrun any value

SELECT 1 FROM TProject WHERE (('2008-12-04' < ISNULL(Actual_Start_DT,Estimated_Start_DT) OR '2008-12-04' >ISNULL( Actual_End_DT, Estimated_End_DT)) OR
('2008-12-19' < ISNULL(Actual_Start_DT,Estimated_Start_DT) OR '2009-12-19' > ISNULL( Actual_End_DT, Estimated_End_DT))
AND Project_NBR=31 AND Active_FG=0)

Can anyone suggest me, how i can change the above query which checks both the conditions appropriately

Thanks Before HAnd

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-19 : 08:10:05

I've tried it using not between.. i got it.. Thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources