Link to home
Start Free TrialLog in
Avatar of jknj72
jknj72

asked on

SQL Syntax

I have a Where Clause that works fine but now I need to check for status 2 and get statusid 2,7 and 10 but im having a little trouble

It was...
Where 
	(@UserID IS NULL OR wwt.UserID = @UserID)
        AND (@StatusID IS NULL OR StatusID = @StatusID) 
	AND (@StartDay IS NULL OR StartDay >= @StartDay)
	AND (@EndDay IS NULL OR EndDay <= @EndDay)
	Order By WWT.WeekID desc

Open in new window


and now it is...
Where 
	(@UserID IS NULL OR wwt.UserID = @UserID)
        AND CASE WHEN @StatusID = 2 THEN StatusID IN(2,7,10) ELSE (@StatusID IS NULL OR StatusID = @StatusID) END
	AND (@StartDay IS NULL OR StartDay >= @StartDay)
	AND (@EndDay IS NULL OR EndDay <= @EndDay)

Open in new window


any help would be appreciated
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Explain at a Barney Level (i.e. 5 year old level) what exactly you are trying to do with the 2, 7, 10, as you can't have an expression where a value is set to an IN range of three other values.
Avatar of Ovidio Pozo
Ovidio Pozo

Please try

Where
      (@UserID IS NULL OR wwt.UserID = @UserID)
        AND (
 (@StatusID IS NULL OR StatusID = @StatusID)
OR
(@StatusID = 2 AND StatusID IN(2,7,10) )
)
      AND (@StartDay IS NULL OR StartDay >= @StartDay)
      AND (@EndDay IS NULL OR EndDay <= @EndDay)
here:

AND (@StatusID IS NULL or (@StatusID =2 and StatusID IN (2,7,10)))

Open in new window

WHERE 
	wwt.UserID = isnull(@UserID, wwt.UserID)
  AND 1 = CASE WHEN @StatusID = 2 THEN case when StatusID IN (2, 7, 10) then 1 else 0 end ELSE case when isnull(@StatusID, StatusID) = StatusID then 1 else 0 END end
	AND StartDay >= isnull(@StartDay, StartDay)
	AND EndDay <= isnull(@EndDay, EndDay)

Open in new window

Avatar of jknj72

ASKER

thanks Shaun...